MySql如何利用父id递归向下查询子节点

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 华北地区往下查询

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。