## 找出所有后代数据
with getTree as (
select parent_id, id from a where a.id = #{id};
UNION all
select a.parent_id, a.id from a inner join getTree on a.parent_id = getTree.id
)
delimiter $$
DROP PROCEDURE IF EXISTS findTreeList;
DROP PROCEDURE IF EXISTS getTreeResult;
CREATE PROCEDURE `findTreeList`( in id varchar(50) )
BEGIN
DECLARE child_id VARCHAR(50) DEFAULT '';
DECLARE done INTEGER DEFAULT 1;
DECLARE cur CURSOR FOR
-- 对应的递归查询，由递归语句改来：select a.parent_id, a.id from a inner join getTree on a.parent_id = getTree.id
-- 此处递归方法主要是找好父子级对应关系，“`findTreeList`( in id varchar(50) )”此参数 id 便是父级id, 匹配的便是子级的parent_id
SELECT a.id FROM a WHERE a.parent_id = id;
DECLARE CONTINUE HANDLER FOR NOT found SET done=0;
INSERT INTO tmp VALUES (tree_id);
SET @@max_sp_recursion_depth = 50;
OPEN cur;
FETCH cur INTO child_id;
WHILE (done=1) DO
CALL findTreeList(child_id);
FETCH cur INTO child_id;
END WHILE;
CLOSE cur;
END;
CREATE PROCEDURE `getTreeResult`( IN id VARCHAR(50) )
DETERMINISTIC
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmp;
-- 结果保存在临时表中
CREATE TEMPORARY TABLE tmp(tree_id VARCHAR(50));
DELETE FROM tmp;
set @tree_id = (SELECT tree_id FROM i_file_type WHERE is_enabled =1 and i_file_type.file_type_id = file_type_id);
-- 开始递归
CALL findTreeList(@tree_id);
-- 可以在此处利用临时表的结果，写其他查询语句
select * from a where a.id = tmp.id;
END;
CALL getTreeResult(#{id});
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP PROCEDURE IF EXISTS findTreeList;
DROP PROCEDURE IF EXISTS getTreeResult;
$$
delimiter ;
