前言
详细的可以参考
https://cloud.tencent.com/developer/article/2106748
这里用 WITH RECURSIVE 实现递归，需要MySQL 8.0 版本以上
示例里没有加逻辑删除字段的筛选，自己加上即可，例如 is_delete = 0
表结构
数据
查询子节点
示例
WITH RECURSIVE recursion AS (
SELECT
sd1.*
FROM
sys_dept sd1
WHERE
sd1.id = '101' UNION ALL
SELECT
sd2.*
FROM
sys_dept sd2,
recursion t2
WHERE
sd2.parent_id = t2.id
) SELECT
t1.*
FROM
recursion t1;
或者
WITH RECURSIVE recursion ( id, NAME, parent_id ) AS (
SELECT
sd1.id,
sd1.NAME,
sd1.parent_id
FROM
sys_dept sd1
WHERE
sd1.id = '101' UNION ALL
SELECT
sd2.id,
sd2.NAME,
sd2.parent_id
FROM
sys_dept sd2,
recursion t2
WHERE
sd2.parent_id = t2.id
) SELECT
t1.id,
t1.NAME,
t1.parent_id
FROM
recursion t1;
结果
不含自己的写法
加个不等于即可
查询父节点
示例
WITH RECURSIVE recursion AS (
SELECT
sd1.*
FROM
sys_dept sd1
WHERE
sd1.id = '103' UNION ALL
SELECT
sd2.*
FROM
sys_dept sd2,
recursion t2
WHERE
sd2.id = t2.parent_id
) SELECT
t1.*
FROM
recursion t1;
结果
解析
其实就是将 parent_id和id 换换位置而已
不含自己的写法
加个不等于即可
