mysql获取父级或子级id

最新推荐文章于 2024-03-13 12:27:04 发布

有点.

最新推荐文章于 2024-03-13 12:27:04 发布

阅读量370

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/anqilaroot/article/details/131053534

版权

获取所有父级id，不包含0

WITH RECURSIVE dept_hierarchy AS (
    SELECT dept_id, parent_id
    FROM structure_dept
    WHERE dept_id = 1664181516737753089
    UNION ALL
    SELECT d.dept_id, d.parent_id
    FROM structure_dept d
             JOIN dept_hierarchy dh ON d.dept_id = dh.parent_id
)
SELECT dept_id FROM dept_hierarchy;

获取所有子级id


SELECT dept_id
FROM (
         SELECT t1.dept_id,
                t1.parent_id,
                IF
                    (find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', dept_id), 0) AS ischild
         FROM (SELECT dept_id, parent_id FROM structure_dept t WHERE t.del_flag = '0' ORDER BY parent_id, dept_id) t1,
              (SELECT @pids := 1664181016390840321) t2
     ) t3
WHERE ischild != 0