mysql查询树节点

最新推荐文章于 2024-07-19 03:47:48 发布

星光汇

最新推荐文章于 2024-07-19 03:47:48 发布

阅读量602

点赞数

分类专栏： SQL 文章标签： mysql mariadb sql

本文链接：https://blog.csdn.net/qq_36617310/article/details/123337052

版权

SQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

根据子节点查询梭鱼的父节点包括当前节点

SELECT T2.*,T1.lvl
FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := parent_id FROM 数据库的表名 WHERE id = _id) AS parent_id, 
                @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := '当前节点的ID', @l := 0) vars,
                数据库的表名 h 
        WHERE @r <> 0) T1 
JOIN 数据库的表名 T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC


示例：

SELECT T2.*,T1.lvl
FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := parent_id FROM jicheng_depart WHERE id = _id) AS parent_id, 
                @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := '1500759729696137217', @l := 0) vars,
                jicheng_depart h 
        WHERE @r <> 0) T1 
JOIN jicheng_depart T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC

通过当前节点查询所有的子节点，不包括当前节点
SELECT
    *
FROM
    (
    SELECT
    t1.id,t1.depart_level,
IF
    ( find_in_set( t1.parent_id, @pids ) > 0, @pids := concat( @pids, ',', t1.id ), 0 ) AS ischild 
FROM
    ( SELECT id, parent_id, depart_level FROM jicheng_depart t WHERE t.del_flag = 0 ORDER BY parent_id, id ) t1,
    ( SELECT @pids := '当前节点的ID' id ) t2 
    ) t3 
WHERE
    t3.ischild != 0
		

jicheng_depart---表名

		id---正常的ID
        parent_id ---父节点