MySQL查询所有父节点与所有子节点

最新推荐文章于 2023-06-14 16:23:22 发布

CXchuanxi

最新推荐文章于 2023-06-14 16:23:22 发布

阅读量2.7k

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/CXchuanxi/article/details/124296226

版权

先贴查询所有父节点

SELECT
           T2.*
       FROM
           (
           SELECT
               @R AS _ID,
               ( SELECT @R := parent_id FROM sys_dept WHERE dept_id = _ID ) AS parent_id,
               @L := @L + 1 AS LVL
           FROM
               ( SELECT @R := #{map.id}, @L := 0 ) VARS,
               sys_dept H
           WHERE
               @R <> 0
           ) T1
           INNER JOIN sys_dept T2 ON T1._ID = T2.dept_id
       WHERE
           t1.parent_id = 324
       ORDER BY
           T1.LVL DESC

where条件看自己需求我这里需要指定

查询所有子节点

           SELECT
       dept_id
   FROM
       (
       SELECT
           t1.dept_id,
       IF
           ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', dept_id ), 0 ) AS ischild
       FROM
           ( SELECT dept_id, parent_id FROM sys_dept t ORDER BY parent_id, dept_id ) t1,
           ( SELECT @pids := #{map.deptid} ) t2
       ) t3
   WHERE
       ischild != 0