最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
MySql利用父id递归向下查询子节点代码方法
时间:2022-06-29 08:34:07 编辑:袖梨 来源:一聚教程网
本篇文章小编给大家分享一下MySql利用父id递归向下查询子节点代码方法,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
不用写存储过程,不用建数据库函数,一段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
测试
--创建测试环境 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;
测试数据展示
查询 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
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
id=4,查询北京下边有哪些地方
最后再从 id=2 华北地区往下查询
相关文章
- 阴阳师小袖之手幽篁流影怎么样 12-23
- 王者荣耀嬴政玄雷天君怎么样 12-23
- 光遇12.23免费魔法有什么 12-23
- 光遇12.23大蜡烛在哪里 12-23
- 光遇12.23季节蜡烛在哪里 12-23
- 王者荣耀嬴政玄雷天君怎么样 王者荣耀嬴政玄雷天君皮肤介绍 12-23