WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;
五、多个 CTE：链式处理
WITH
active_emps AS (
SELECT * FROM employees WHERE hire_date >= '2023-01-01'
),
it_emps AS (
SELECT * FROM active_emps WHERE department = 'IT'
),
high_performers AS (
SELECT *, salary * 1.1 AS bonus
FROM it_emps WHERE salary > 90000
)
SELECT name, salary, bonus FROM high_performers;
六、递归 CTE：处理层级数据（杀手级！）
语法
WITH RECURSIVE cte_name AS (
-- 锚点查询（起点）
SELECT ... WHERE manager_id IS NULL
UNION [ALL]
-- 递归部分
SELECT e.* FROM employees e
JOIN cte_name c ON e.manager_id = c.id
)
SELECT * FROM cte_name;
示例：查询 CEO 及其所有下属
WITH RECURSIVE org_chart AS (
-- 锚点：CEO
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归：下属
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
LPAD(' ', level * 2) || name AS hierarchy,
level,
department
FROM org_chart
ORDER BY level, name;
输出：
hierarchy | level | department
------------------------+-------+------------
CEO | 0 | Executive
CTO | 1 | IT
CFO | 1 | Finance
IT Manager | 2 | IT
Finance Manager | 2 | Finance
Dev Lead | 3 | IT
Accountant | 3 | Finance
Alice | 4 | IT
Bob | 4 | IT
Carol | 4 | Finance
七、递归控制：防止无限循环
-- 限制最大深度
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 0 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
WHERE oc.level < 10 -- 最大 10 层
)
SELECT * FROM org_chart;
八、物化 CTE（PG12+）：性能控制
选项
说明
MATERIALIZED
强制物化（存储临时结果）
NOT MATERIALIZED
强制内联（不存储）
-- 强制物化：适合大表中间结果
WITH high_salary AS MATERIALIZED (
SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) FROM high_salary GROUP BY department;
-- 强制内联：适合小表
WITH temp AS NOT MATERIALIZED (
SELECT id, name FROM employees WHERE id < 10
)
SELECT * FROM temp;
九、CTE vs 子查询 vs 临时表
方式
可读性
性能
适用场景
CTE
5 stars
中等
复杂逻辑、可读性优先
子查询
2 stars
中等
简单嵌套
临时表
3 stars
高
超大中间结果、多次使用
十、性能优化
1. 索引支持
-- 为递归连接列建索引
CREATE INDEX idx_emp_manager ON employees(manager_id);
-- 为 WHERE 列建索引
CREATE INDEX idx_emp_dept ON employees(department);
2. 避免重复计算
-- 错误：重复计算
WITH cte1 AS (SELECT expensive_func() FROM t),
cte2 AS (SELECT expensive_func() FROM t)
SELECT ...;
-- 正确：一次计算
WITH cte AS (SELECT expensive_func() AS val FROM t),
cte1 AS (SELECT val FROM cte),
cte2 AS (SELECT val FROM cte)
SELECT ...;
十一、JSON 与数组 CTE 应用
-- 聚合部门信息为 JSON
WITH dept_stats AS (
SELECT
department,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary,
JSONB_AGG(
JSONB_BUILD_OBJECT('name', name, 'salary', salary)
) AS employees
FROM employees
GROUP BY department
)
SELECT * FROM dept_stats;
十二、常见陷阱与解决方案
陷阱
说明
解决方案
CTE 被多次执行
每个引用都重新计算
用 MATERIALIZED 或临时表
递归死循环
环路数据
加 level 限制
CTE 引用顺序错误
后定义先用
按依赖顺序写
物化导致内存爆炸
中间结果太大
用 NOT MATERIALIZED 或临时表
十三、最佳实践 Checklist
项目
建议
复杂查询必用 CTE
提升可读性
递归加 level 限制
防止死循环
大结果用 MATERIALIZED
避免重复计算
小结果用 NOT MATERIALIZED
减少 I/O
建连接索引
manager_id 等
命名清晰
dept_stats, org_chart
单独测试 CTE
WITH cte AS (...) SELECT * FROM cte;
十四、一键组织架构报表
WITH RECURSIVE org AS (
SELECT id, name, manager_id, department, salary, 0 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, e.department, e.salary, o.level + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
WHERE o.level < 20
),
dept_summary AS (
SELECT
department,
COUNT(*) AS headcount,
SUM(salary) AS payroll,
AVG(salary) AS avg_salary
FROM org
GROUP BY department
)
SELECT
LPAD(' ', level * 3) || name AS employee,
department,
salary,
level
FROM org
UNION ALL
SELECT
'--- ' || department || ' SUMMARY' AS employee,
department,
payroll,
NULL
FROM dept_summary
ORDER BY department, level NULLS FIRST, employee;
十五、快速实战：5 分钟掌握 WITH
-- 1. 普通 CTE：IT 部门平均薪资
WITH it_avg AS (
SELECT AVG(salary) AS avg_sal FROM employees WHERE department = 'IT'
)
SELECT name, salary FROM employees, it_avg WHERE salary > avg_sal;
-- 2. 递归 CTE：列出 Dev Lead 的所有下属
WITH RECURSIVE team AS (
SELECT id, name, 0 AS level FROM employees WHERE name = 'Dev Lead'
UNION ALL
SELECT e.id, e.name, t.level + 1
FROM employees e
JOIN team t ON e.manager_id = t.id
)
SELECT name, level FROM team;
-- 3. 物化 CTE
WITH high_paid AS MATERIALIZED (
SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) FROM high_paid GROUP BY department;
现在就动手： 在 employees 表中：
用 CTE 统计 每个部门的员工数和平均薪资
用递归 CTE 列出 CTO 的完整下属树（带层级）
用 MATERIALIZED 优化一个大查询
建索引并用 EXPLAIN 验证递归性能
十六、性能对比（EXPLAIN）
-- 慢：无索引递归
EXPLAIN ANALYZE WITH RECURSIVE ... SELECT * FROM org_chart;
-- 快：有索引
CREATE INDEX idx_emp_manager ON employees(manager_id);
EXPLAIN ANALYZE WITH RECURSIVE ... SELECT * FROM org_chart;