什么是 WITH 查询

WITH 查询，也叫 CTE（Common Table Expression，公用表表达式），说白了就是给临时结果起个名字。

普通的子查询长这样：

SELECT*
FROM employees
WHERE salary >(
SELECTAVG(salary)
FROM employees
);

用 WITH 改写后：

WITH avg_salary AS(
SELECTAVG(salary)as avg_val
FROM employees
)
SELECT e.*
FROM employees e, avg_salary
WHERE e.salary > avg_salary.avg_val;

看起来代码变长了？确实。但当你的查询变得复杂时，这种"长"反而是一种清晰。

真正体现价值的地方

假设你要做一个销售报表，需要：

1. 先算出每个销售员的销售额
2. 再算整体的平均销售额
3. 最后对比每个人是高于还是低于平均

不用 WITH，你得这么写：

SELECT
    e.name,
(SELECTSUM(amount)FROM sales WHERE employee_id = e.id)as personal_sales,
CASE
WHEN(SELECTSUM(amount)FROM sales WHERE employee_id = e.id)>
(SELECTAVG(total)FROM(
SELECTSUM(amount)as total
FROM sales
GROUPBY employee_id
) t)
THEN'Above Average'
ELSE'Below Average'
ENDas performance
FROM employees e;

同一个子查询写了三遍。维护的时候改一个地方，另外两个很容易漏掉。

用 WITH 重构：

WITH
employee_sales AS(
SELECT employee_id,SUM(amount)as total_sales
FROM sales
GROUPBY employee_id
),
avg_sales AS(
SELECTAVG(total_sales)as avg_amount
FROM employee_sales
)
SELECT
    e.name,
    es.total_sales,
CASE
WHEN es.total_sales > avs.avg_amount THEN'Above Average'
ELSE'Below Average'
ENDas performance
FROM employees e
JOIN employee_sales es ON e.id = es.employee_id
CROSSJOIN avg_sales avs;

每个逻辑块都有名字，想改哪里改哪里。这就是 WITH 的价值——不是为了少写代码，而是为了让代码好懂、好改。

递归查询：处理层级数据的神器

WITH 还有一个隐藏技能：递归。

组织架构、分类目录、评论回复……这些有层级关系的数据，用普通 SQL 查询很麻烦。但用递归 CTE，几行代码就能搞定。

假设有个员工表，每个人有个 manager_id 指向自己的上级：

CREATETABLE employees (
    id INT,
    name VARCHAR(50),
    manager_id INT
);

你想查出某个员工的所有下属（包括间接下属），传统写法基本不可能，递归 CTE 却能轻松解决：

WITH RECURSIVE subordinates AS(
-- 锚点：从目标员工开始
SELECT id, name, manager_id,1aslevel
FROM employees
WHERE id =1-- 假设要查 ID 为 1 的员工

UNIONALL

-- 递归：找到向上一层的员工汇报的人
SELECT
        e.id,
        e.name,
        e.manager_id,
        s.level+1
FROM employees e
INNERJOIN subordinates s ON e.manager_id = s.id
)
SELECT*FROM subordinates;

这个查询的执行过程是这样的：

1. 先找到 ID=1 的员工（锚点）
2. 找到 manager_id=1 的人（第一层下属）
3. 找到 manager_id 等于第一层下属 ID 的人（第二层）
4. 以此类推，直到没有更多匹配

递归查询有两个关键点：

• UNION ALL 连接锚点和递归部分
• 必须有终止条件，否则可能死循环（比如上面例子中，如果没有更多下级，递归就停了）

反向查询：找上级

有时候你需要反着来——给定一个员工，找出他的所有上级直到 CEO。

WITH RECURSIVE management_chain AS(
-- 从具体员工开始
SELECT id, name, manager_id,1as depth
FROM employees
WHERE name ='张三'

UNIONALL

-- 向上找上级
SELECT
        m.id,
        m.name,
        m.manager_id,
        mc.depth +1
FROM employees m
JOIN management_chain mc ON m.id = mc.manager_id
)
SELECT name, depth
FROM management_chain
ORDERBY depth DESC;

这个技巧在实际工作中特别实用。比如你要做权限系统，经常需要查"某人的所有上级"或者"某人的所有下属"。

一些需要注意的地方

性能问题

大部分数据库对 CTE 的处理方式是"内联"——就是把 CTE 的定义复制到引用它的地方。如果一个 CTE 被引用了三次，它可能会被执行三次。

PostgreSQL 12+ 和 DuckDB 支持 MATERIALIZED 关键字，可以强制只计算一次：

WITH expensive_cte AS MATERIALIZED (
SELECT deptid,SUM(salary)as total
FROM employees
GROUPBY deptid
)
SELECT*FROM expensive_cte;

递归深度限制

SQL Server 默认递归最多 100 层。超过会报错。可以用 OPTION (MAXRECURSION N) 调整：

WITH RECURSIVE cte AS(...)
SELECT*FROM cte
OPTION(MAXRECURSION 1000);

MySQL 的支持

MySQL 8.0 之前不支持 CTE，8.0 之后才加入。如果你还在用老版本的 MySQL，是时候升级了。

写在最后

WITH 查询不是什么高深技术，但它是那种"一旦学会就回不去"的工具。就像有了电动车之后不想骑自行车一样，写了 WITH 之后再看那些层层嵌套的子查询，真的会觉得头疼。

它不会让你的 SQL 执行得更快（有时候反而慢一点），但会让你的 SQL 写起来更爽、读起来更顺、维护起来更轻松。

对于写代码这件事来说，这往往比那点性能差异重要得多。

快速检查清单

• 查询超过 50 行？考虑用 WITH 拆分
• 同一个子查询用了两次以上？肯定要用 WITH
• 查组织架构或层级关系？递归 CTE 是首选
• 性能敏感且 CTE 被多次引用？试试 MATERIALIZED

参考来源：

• PostgreSQL Documentation: WITH Queries
• SQL WITH Clause - GeeksforGeeks
• Common Table Expressions (CTE) in SQL - Analytics Vidhya