1. 递归查询说明
Oracle
-- 使用递归查询获取员工的所有下属
WITH subordinates (employee_id, subordinate_id, level) AS (
SELECT employee_id, subordinate_id, 1
FROM employee
WHERE employee_id = 1 -- 以员工ID为1为例
UNION ALL
SELECT e.employee_id, e.subordinate_id, s.level + 1
FROM employee e
JOIN subordinates s ON e.employee_id = s.subordinate_id
)
SELECT employee_id, subordinate_id, level
FROM subordinates;
Oracle使用WITH
关键字来创建递归查询,首先查询出初始条件(即以某个员工ID为起点),然后通过UNION ALL
将其和子查询(即下属)拼接起来,直到查询出所有下属。
MySQL
-- 使用递归查询获取员工的所有下属
WITH RECURSIVE subordinates (employee_id, subordinate_id, level) AS (
SELECT employee_id, subordinate_id, 1
FROM employee
WHERE employee_id = 1 -- 以员工ID为1为例
UNION ALL
SELECT e.employee_id, e.subordinate_id, s.level + 1
FROM employee e
JOIN subordinates s ON e.employee_id = s.subordinate_id
)
SELECT employee_id, subordinate_id, level
FROM subordinates;
MySQL使用WITH RECURSIVE
关键字来创建递归查询,其语法与Oracle类似。
SQL Server
-- 使用递归查询获取员工的所有下属
WITH subordinates (employee_id, subordinate_id, level) AS (
SELECT employee_id, subordinate_id, 1
FROM employee
WHERE emplo