记录一下MySQL8.0以上SQL查一个树任意节点下所有子节点 通过SQL的递归实现
- 表结构
create table employees
(
id int auto_increment primary key,
name varchar(255) null,
manager_id int null
);
- 数据插入语句
INSERT INTO employees (id, name, manager_id) VALUES (1, '1a', 0);
INSERT INTO employees (id, name, manager_id) VALUES (2, '1b', 0);
INSERT INTO employees (id, name, manager_id) VALUES (3, '2a', 1);
INSERT INTO employees (id, name, manager_id) VALUES (4, '2b', 1);
INSERT INTO employees (id, name, manager_id) VALUES (5, '2c', 1);
INSERT INTO employees (id, name, manager_id) VALUES (6, '3a', 5);
INSERT INTO employees (id, name, manager_id) VALUES (7, '3b', 5);
INSERT INTO employees (id, name, manager_id) VALUES (8, '3c', 4);
INSERT INTO employees (id, name, manager_id) VALUES (9, '3d', 4);
INSERT INTO employees (id, name, manager_id) VALUES (10, '3e', 4);
- 查询语句
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 4
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
- WITH 声明可复用sql
- WITH RECURSIVE 声明可递归查询sql
