mysql的递归写法：部门层级

前言

详细的可以参考：https://cloud.tencent.com/developer/article/2106748
这里用 WITH RECURSIVE 实现递归，需要 MySQL 8.0 版本以上
示例里没有加逻辑删除字段的筛选，自己加上即可，例如 is_delete = 0
oracle的递归看我另一篇博文：https://www.cnblogs.com/daen/p/17252462.html

表结构

数据

查询子节点

示例

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 换换位置而已

多行合并一行

就是我输入部门ID，然后获取到他的所有的父级的部门名称，拼接成一个字符串

示例

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
	group_concat( t1.`name` ORDER BY t1.`level` ASC SEPARATOR '#' ) AS nameList 
FROM
	recursion t1;

结果