mysql查询上(下)级部门

1.利用祖级列表(ancestors)字段：

1.1准备

CREATE TABLE `my_dept` (
  `dept_id` int NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `parent_id` int DEFAULT NULL COMMENT '上级部门',
  `ancestors` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '祖级列表',
  `dept_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称',
  `sort` int DEFAULT NULL COMMENT '排序',
  `status` bit(1) NOT NULL COMMENT '状态',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

部门表中有个祖级列表字段，用来描述所有上级节点。

1.2 查询下级

#查询部门ID为1的下级部门
SELECT * FROM `my_dept` WHERE FIND_IN_SET(1,ancestors)

我们对语句进行拆解，顺便说说FIND_IN_SET函数。
FIND_IN_SET(str,strlist)，用来查询左侧字符串str是否在右侧字符串列表strlist中，如果存在则返回第一次出现的位置，否则返回0。
示例：

我们知道查询的时候以数字0或非0可以做为过滤条件


再看我们查询下级部门的语句

1.3 查询上级：

SELECT * from my_dept
WHERE  FIND_IN_SET(dept_id,
	(SELECT CONCAT(ancestors,',',dept_id) FROM my_dept WHERE dept_id =8)
)	

2.with recursive as递归查询（Mysql8）

2.1 准备

先在表中增加数据加深层级

mysql8新增了递归查询方法

递归写法：

WITH recursive 表名 AS ( 
	初始语句（非递归部分） 
	UNION ALL 
	递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]

2.2 查询下级：

-- 递归查询下级部门
WITH RECURSIVE tree_cte as
 (
				 select dept_id, dept_name, parent_id, 0 as lv
				 from my_dept
				 where dept_id = 1

				 UNION ALL

				 select t.dept_id, t.dept_name, t.parent_id, lv + 1
				 from my_dept t
				 join tree_cte tcte on t.parent_id = tcte.dept_id 
 )
 SELECT * from tree_cte ORDER BY lv 

语句拆解：

2.3 查询上级

#递归查询上级部门
WITH RECURSIVE tree_cte as
 (
				 select dept_id, dept_name, parent_id, 0 as lv
				 from my_dept
				 where dept_id = 8

				 UNION ALL

				 select t.dept_id, t.dept_name, t.parent_id, lv + 1
				 from my_dept t
				 join tree_cte tcte on t.dept_id = tcte.parent_id and t.dept_id !=0
 )
 SELECT * from tree_cte ORDER BY lv desc

3.局部变量递归查询

局部变量使用示例：

查询示例：

select t3.* from (
	select t1.dept_id,t1.parent_id,t1.dept_name,
	if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', dept_id), 0) as ischild
	from 
	(
			 select dept_id,parent_id,dept_name from my_dept t order by parent_id, dept_id		
	) t1,
	(select @pids := 5) t2
 ) t3 where ischild != 0 or dept_id = 5
 

sql拆解：

查询上级部门：

加ID

select t4.* from (
	select @pids as dept_id,
	(SELECT @pids := parent_id FROM my_dept WHERE dept_id = @pids) as pid
	from 
	(
			 select dept_id,parent_id,dept_name from my_dept t 
	) t1,
	(select @pids := 9) t2 
 ) t3
 JOIN
 my_dept t4 
 on t4.dept_id = t3.dept_id 

 select t4.* from (
	select @pids as dept_id,
	(SELECT @pids := parent_id FROM my_dept WHERE dept_id = @pids) as pid,
	@lv := @lv+1 as lv
	from 
	(
			 select dept_id,parent_id,dept_name from my_dept t 
	) t1,
	(select @pids := 9, @lv := 0) t2 
 ) t3
 JOIN
 my_dept t4 
 on t4.dept_id = t3.dept_id ORDER BY lv desc