sql递归查询

✅ 基本示例：合并两个查询结果

🧾 示例数据：

假设有两个表：

CREATE TABLE fruits1 (name VARCHAR(20));
CREATE TABLE fruits2 (name VARCHAR(20));

INSERT INTO fruits1 VALUES ('apple'), ('banana');
INSERT INTO fruits2 VALUES ('banana'), ('cherry');

🔹 使用 UNION ALL

SELECT name FROM fruits1
UNION ALL
SELECT name FROM fruits2;

🔽 结果：

apple
banana
banana   -- ✅ 没有去重
cherry

🔹 使用 UNION（对比）

SELECT name FROM fruits1
UNION
SELECT name FROM fruits2;

🔽 结果：

apple
banana   -- ⚠️ 重复项被去掉
cherry

✅ 递归结构中使用 UNION ALL

🌲 示例：员工上下级结构递归

CREATE TABLE employees (
id INT,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', NULL),     -- CEO
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 2),
(5, 'Eva', 3);

📌 查出某人（如 Alice）及其所有下属：

WITH RECURSIVE hierarchy AS (
SELECT id, name, manager_id
FROM employees
WHERE name = 'Alice'

UNION ALL

SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
)

SELECT * FROM hierarchy;

🔽 结果：

id | name    | manager_id
---|---------|------------
1  | Alice   | NULL
2  | Bob     | 1
3  | Charlie | 2
4  | David   | 2
5  | Eva     | 3

✅ UNION ALL 是必须的，否则递归层级会被“去重”提前终止。

🧠 总结

CTE（Common Table Expression，公共表表达式）是 SQL 中的一种临时结果集，它可以在一个查询的开头定义，并在后续的查询中像一个虚拟表一样使用，主要用于提高 SQL 可读性、结构化复杂逻辑，支持递归查询。

🧾 基本语法

WITH cte_name AS (
SELECT ...
FROM ...
WHERE ...
)
SELECT *
FROM cte_name
WHERE ...;

✅ CTE 的核心用途

📘 示例 1：基本 CTE（替代子查询）

📌 需求：查找工资高于平均值的员工

不使用 CTE：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

使用 CTE：

WITH avg_salary AS (
SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name, salary
FROM employees, avg_salary
WHERE salary > avg_salary.avg_sal;

✅ 可读性更好，逻辑更清晰。

📘 示例 2：递归 CTE（递归查树）

表结构（员工和上级）：

id | name    | manager_id
---|---------|------------
1  | Alice   | NULL
2  | Bob     | 1
3  | Carol   | 2
4  | Dave    | 3

查找某个员工（如 Alice）下所有下属：

WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id
FROM employees
WHERE name = 'Alice'

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;

🔽 输出：

1 | Alice  | NULL
2 | Bob    | 1
3 | Carol  | 2
4 | Dave   | 3

✅ 递归 CTE 的关键：使用 UNION ALL，并引用自己。

🚀 CTE vs 子查询

🧠 总结

• CTE 是一个临时的命名结果集，可在主查询中使用；
• 使用 WITH 语法；
• 支持普通查询和递归结构；
• 对于多层嵌套查询或流程树、组织结构图等非常有用；
• 可以在 MySQL 8.0+、PostgreSQL、SQLite 3.8+、SQL Server 中使用。