56,677
社区成员
发帖
与我相关
我的任务
分享
mysql> DELIMITER $$
mysql> drop procedure if exists p_dir_info$$
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create procedure p_dir_info(in user varchar(20), in parent_path varchar(255))
-> begin
-> select * from
-> (select d.user, d.path, d.parent_path, d.dir, d.create_time,
-> (case isnull(sum(f.count)) when true then 0 else sum(f.count) end) as count,
-> (case isnull(sum(f.size)) when true then 0 else sum(f.size) end) as size
-> from t_directories d_left join
-> (select path, count(file) as count, sum(size) from t_files group by path) f on instr(f.path, d.path) = 1
-> where d.user = user and d.parent_path = parent_path group by path) dir order by create_time;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql>