mysql树形结构查询子节点

雨点打透心脏的1/2处 2023-05-29 14:27 62阅读 0赞

### 查询所有子节点 ###

需求：

在树形的节点关系下，比如菜单树或者文件目录树，要想获取某个节点的所有子节点，或者所有父类节点，在知道节点树最大层级的情况下，可以直接通过一条sql直接查询实现

表结构：  
id, parent\_id

eg: 已知节点树深度不超过10， 查询id=100010的节点的所有子节点

select
    	org.id AS id,
    	concat(
    	   '[',
    	    concat_ws(',',
    			group_concat( DISTINCT pa1.id ),
    			group_concat( DISTINCT pa2.id ),
    			group_concat( DISTINCT pa3.id ),
    			group_concat( DISTINCT pa4.id ),
    			group_concat( DISTINCT pa5.id ),
    			group_concat( DISTINCT pa6.id ),
    			group_concat( DISTINCT pa7.id ),
    			group_concat( DISTINCT pa8.id ),
    			group_concat( DISTINCT pa9.id ),
    			group_concat( DISTINCT pa10.id ) ),
    		']' 
    	) AS child_ids 
    from organization org
    left join organization pa1 on org.id=pa1.parent_id
    left join organization pa2 on pa1.id=pa2.parent_id
    left join organization pa3 on pa2.id=pa3.parent_id
    left join organization pa4 on pa3.id=pa4.parent_id
    left join organization pa5 on pa4.id=pa5.parent_id
    left join organization pa6 on pa5.id=pa6.parent_id
    left join organization pa7 on pa6.id=pa7.parent_id
    left join organization pa8 on pa7.id=pa8.parent_id
    left join organization pa9 on pa8.id=pa9.parent_id
    left join organization pa10 on pa9.id=pa10.parent_id
    where org.id=100010
    group by org.id;

结果：

id, child\_ids

100, \[1,2,3,4\]

### 查询所有父节点 ###

已知节点树深度不超过10， 查询id=100038的节点的所有父节点

select
    	org.id,
    	concat(
    		'[',
    		concat_ws(',',
    			zo.parent_id,
    			pa1.parent_id,
    			pa2.parent_id,
    			pa3.parent_id,
    			pa4.parent_id,
    			pa5.parent_id,
    			pa6.parent_id,
    			pa7.parent_id,
    			pa8.parent_id,
    			pa9.parent_id,
    			pa10.parent_id ),
    	   ']' 
    	) AS parent_ids 
    from organization org
    left join organization pa1 on pa1.id=org.parent_id
    left join organization pa2 on pa2.id=pa1.parent_id
    left join organization pa3 on pa3.id=pa2.parent_id
    left join organization pa4 on pa4.id=pa3.parent_id
    left join organization pa5 on pa5.id=pa4.parent_id
    left join organization pa6 on pa6.id=pa5.parent_id
    left join organization pa7 on pa7.id=pa6.parent_id
    left join organization pa8 on pa8.id=pa7.parent_id
    left join organization pa9 on pa9.id=pa8.parent_id
    left join organization pa10 on pa10.id=pa9.parent_id
    where org.id=100038

结果：

id, parent\_ids

100, \[1,2,3,4\]

> 注  
> GROUP\_CONCAT(field\_name SEPARATOR ‘,’) 分组合并某列， 即将分组后每组数据的某列合并为一行数据（多行数据合并为一行，只针对一列）  
> GROUP\_CONCAT()中的值为你要合并的数据的字段名;  
> SEPARATOR 函数是用来分隔这些要合并的数据的；  
> ’ '中是你要用哪个符号来分隔，默认使用 ','分隔；

> 注：group\_concat只有与group by语句同时使用才能产生效果，需要将拼接的结果去重的话，可与distinct结合使用即可。