目录
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