mysql树转查询，根据id查询所有子孙级的数据

缺乏、安全感 2023-10-06 14:11 10阅读 0赞

需求: 查出当前用户下面所有的子用户信息. ( 传递父类id )  
分析:  
方法一: 使用Java程序进行遍历查询. ( 一条一条查询, 执行效率低 )  
方法二: sql递归查询. ( 单句查询, 效率比遍历查询高! )

1.库表结构如下: ( 自关联的 - 无限级用户表 )

![å¨è¿éæå¥å¾çæè¿°][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyOTg2MTA3_size_16_color_FFFFFF_t_70]  
2.sql语句实现需求:  
2.1 **查所有的叶子节点. ( 不含自己 )**，下面红色的地方，需要替换自己的属性和表名以及id值  
SELECT u2.id, u2.name  
FROM(  
SELECT  
@ids AS p\_ids,  
(SELECT @ids := GROUP\_CONCAT(id) FROM test\_user WHERE FIND\_IN\_SET(parentId, @ids)) AS c\_ids,  
@l := @l+1 AS LEVEL  
FROM test\_user, (SELECT @ids := '101', @l := 0 ) b \#此处为需要传递的父类id.  
WHERE @ids IS NOT NULL  
) u1  
JOIN test\_user u2  
ON FIND\_IN\_SET(u2.id, u1.p\_ids) AND u2.id != '101' \#需要包含自己, 则删掉 !=  
  
2.2 **查所有的父节点. ( 含自己 )**，下面红色的地方，需要替换自己的属性和表名以及id值  
SELECT u2.id, u2.name  
FROM(  
SELECT  
@id AS c\_ids,  
( SELECT @id := parentId FROM test\_user WHERE id = @id ) AS p\_ids,  
@l := @l+1 AS LEVEL  
FROM test\_user, (SELECT @id := '105', @l := 0 ) b  
WHERE @id > 0  
) u1  
JOIN test\_user u2 ON u1.c\_ids = u2.id

2.3**查所有的父节点. ( 不含自己 )**，下面红色的地方，需要替换自己的属性和表名以及id值  
SELECT u2.id, u2.name  
FROM(  
SELECT  
@id AS c\_ids,  
( SELECT @id := parentId FROM test\_user WHERE id = @id ) AS p\_ids,  
@l := @l+1 AS LEVEL  
FROM test\_user, (SELECT @id := '105', @l := 0 ) b  
WHERE @id > 0  
) u1  
JOIN test\_user u2 ON u1.c\_ids = u2.id AND u2.id != '105'

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyOTg2MTA3_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190921181701490.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyOTg2MTA3,size_16,color_FFFFFF,t_70