mysql递归查询（父级，子集）

①查询父级

表：

先给表创建函数：

 1 CREATE FUNCTION `getParentList`(rootId varchar(100))   
 2 RETURNS varchar(1000)   
 3 BEGIN   
 4 DECLARE fid varchar(100) default '';   
 5 DECLARE str varchar(1000) default rootId;   
 6   
 7 WHILE rootId is not null  do   
 8     SET fid =(SELECT PID FROM DIGUI WHERE ID = rootId);   
 9     IF fid is not null THEN   
10         SET str = concat(str, ',', fid);      
11         SET rootId = fid;   
12     ELSE   
13         SET rootId = fid;   
14     END IF;   
15 END WHILE;   
16 return str;  
17 END

然后可以查询父级了：

1 SELECT
2     *
3 FROM
4     digui
5 WHERE
6     FIND_IN_SET(id, getParentList('9'))

 ②查询子集（参考：https://www.cnblogs.com/rainydayfmb/p/8028868.html）

表：

SQL：查询id=12的子集

 1 SELECT
 2     t3.id,
 3     t3.parent_id,
 4     t3.menu_name 
 5 FROM
 6     (
 7     SELECT
 8         t1.id,
 9         t1.parent_id,
10         t1.menu_name,
11     IF
12         ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild 
13     FROM
14         ( SELECT id, parent_id, menu_name FROM menu t WHERE t.STATUS = 1 ORDER BY parent_id, id ) t1,
15         ( SELECT @pids := 12 ) t2 
16     ) t3 
17 WHERE
18     ischild != 0

结果：

 

ps:oracle递归查询：https://www.cnblogs.com/lgqrlchinese/p/11381766.html

posted @ 2019-11-03 18:18  龙谷情Sinoam  阅读(7951)  评论(0)  编辑  收藏  举报
Smiley face