MySQL8级联查询
# 创建调试数据
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT,
salary DECIMAL(10, 2)
);
INSERT INTO employees (id, name, manager_id, salary) VALUES
(1, 'Alice', NULL, 50000.00),
(2, 'Bob', 1, 40000.00),
(3, 'Charlie', 1, 45000.00),
(4, 'Dave', 2, 35000.00),
(5, 'Eve', 3, 30000.00),
(6, 'Frank', 3, 32000.00),
(7, 'Grace', 6, 28000.00),
(8, 'Henry', 6, 29000.00),
(9, 'Ivy', 8, 25000.00),
(10, 'Jack', 8, 26000.00);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 给定数据,向下级联查询
注意看第四行和第八行的写法!!!!!!!!!!
WITH RECURSIVE employee_tree AS (
SELECT id, name, manager_id, salary, 0 AS level
FROM employees
WHERE manager_id = 3
UNION ALL
SELECT e.id, e.name, e.manager_id, e.salary, et.level + 1
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id
)
SELECT id, name, manager_id, salary, level
FROM employee_tree
ORDER BY level, id;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 给定数据,向上级联查询
注意看第四行和第八行的写法!!!!!!!!!!
WITH RECURSIVE employee_tree AS (
SELECT id, name, manager_id, salary, 0 AS level
FROM employees
WHERE id = 3
UNION ALL
SELECT e.id, e.name, e.manager_id, e.salary, et.level + 1
FROM employees e
JOIN employee_tree et ON e.id = et.manager_id
)
SELECT id, name, manager_id, salary, level
FROM employee_tree
ORDER BY level, id;
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
编辑 (opens new window)
上次更新: 2023/06/08, 11:37:53