mysql递归查询(父级,子集)

时间:2024-03-17 12:31:36

①查询父级

表:

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'))

mysql递归查询(父级,子集)

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

表:

mysql递归查询(父级,子集)

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

结果:

mysql递归查询(父级,子集)

 

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