MySql通过父id递归向下查询子节点

客官°小女子只卖身不卖艺 2023-09-30 09:44 2阅读 0赞

**不用写存储过程，不用建数据库函数，一段sql就可以实现  
不用写存储过程，不用建数据库函数，一段sql就可以实现  
不用写存储过程，不用建数据库函数，一段sql就可以实现**

SELECT
    	ID.LEVEL,
    	DATA.* 
    FROM
    	(
    	SELECT
    		@ids AS _ids,
    		( SELECT @ids := GROUP_CONCAT( region_id ) FROM region WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids,
    		@l := @l + 1 AS LEVEL 
    	FROM
    		region,
    		( SELECT @ids := 3, @l := 0 ) b 
    	WHERE
    		@ids IS NOT NULL 
    	) ID,
    	region DATA 
    WHERE
    	FIND_IN_SET( DATA.region_id, ID._ids ) 
    ORDER BY
    	LEVEL

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16]

> 测试

--创建测试环境
    create table t_test(
    	id int PRIMARY key,
    	parent_id int,
    	name varchar(200)
    )
    
    insert t_test VALUES(1,null,"中国");
    
    insert t_test VALUES(2,1,"华北");
    
    insert t_test VALUES(3,2,"山西省");
    insert t_test VALUES(4,2,"北京");
    
    insert t_test VALUES(5,3,"临汾市");
    insert t_test VALUES(6,4,"北京市");
    
    
    insert t_test VALUES(7,5,"尧都区");
    insert t_test VALUES(8,6,"朝阳区");
    
    insert t_test VALUES(9,7,"解放西路");
    insert t_test VALUES(10,8,"朝阳北路");
    
    
    SELECT * FROM t_test;

测试数据展示  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_13_color_FFFFFF_t_70_g_se_x_16]  
查询 id=1，查询中国下边有哪些地方

SELECT
    	ID.LEVEL,
    	DATA.* 
    FROM
    	(
    	SELECT
    		@ids AS _ids,
    		( SELECT @ids := GROUP_CONCAT( id ) FROM t_test WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids,
    		@l := @l + 1 AS LEVEL 
    	FROM
    		t_test,
    		( SELECT @ids := 1, @l := 0 ) b 
    	WHERE
    		@ids IS NOT NULL 
    	) ID,
    	t_test DATA 
    WHERE
    	FIND_IN_SET( DATA.id, ID._ids ) 
    ORDER BY
    	LEVEL

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
id=3，查询山西下边有哪些地方

SELECT
    	ID.LEVEL,
    	DATA.* 
    FROM
    	(
    	SELECT
    		@ids AS _ids,
    		( SELECT @ids := GROUP_CONCAT( id ) FROM t_test WHERE FIND_IN_SET(parent_id, @ids ) ) AS cids,
    		@l := @l + 1 AS LEVEL 
    	FROM
    		t_test,
    		( SELECT @ids := 3, @l := 0 ) b 
    	WHERE
    		@ids IS NOT NULL 
    	) ID,
    	t_test DATA 
    WHERE
    	FIND_IN_SET( DATA.id, ID._ids ) 
    ORDER BY
    	LEVEL

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
id=4，查询北京下边有哪些地方  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 3]  
最后再从 id=2 华北地区往下查询  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 4]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/716fb84a20d34045a64497a476894e12.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCn5LiK5pyJ5Yac,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_13_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ffa2b44737e6487182d9f8c1cffad929.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCn5LiK5pyJ5Yac,size_13,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/0139e75f7e8645a791752c88a1debd42.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCn5LiK5pyJ5Yac,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/2a920a4aa510445e8509cbb3a26fdf0e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCn5LiK5pyJ5Yac,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/d3ece80be03849ba89f62d76fe8e4c9a.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCn5LiK5pyJ5Yac,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5bCn5LiK5pyJ5Yac_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/3aaa277e8d92421687bae79d21cbc9e0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCn5LiK5pyJ5Yac,size_20,color_FFFFFF,t_70,g_se,x_16