SQL WITH查询实战：简化复杂数据处理与层级数据查询

在处理数据时，你是否曾被多层嵌套、冗长复杂的 SQL 查询搞得头晕目眩？当同一个子查询逻辑在 SELECT、WHERE、JOIN 中反复出现时，不仅代码难以阅读，维护起来更是噩梦。今天，我们就来聊聊 SQL 中的一个“秘密武器”——WITH 查询（又称公用表表达式，CTE），看看它是如何将复杂查询化繁为简的。

什么是 WITH 查询

WITH 查询，其正式名称是公用表表达式（Common Table Expression, CTE）。简单来说，它允许你为查询结果集命名，并在后续的主查询中像使用普通表一样引用它。

传统的子查询写法是这样的：

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

使用 WITH 进行改写后：

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

乍一看，代码似乎变长了？确实如此。但当你的查询逻辑逐渐复杂时，这种“模块化”的“长”反而代表着更高的清晰度和可维护性。这不仅仅是语法糖，而是一种思维方式的转变。

真正体现价值的地方

让我们通过一个更贴近实际的例子来体会 WITH 查询的价值。假设你需要生成一份销售业绩报告，逻辑如下：

1. 计算每位销售人员的总销售额。
2. 计算所有销售人员的平均销售额。
3. 对比每个人的销售额，判断其是否高于平均水平。

如果不用 WITH，查询可能会写成这样：

SELECT
    e.name,
    (SELECT SUM(amount) FROM sales WHERE employee_id = e.id) as personal_sales,
    CASE
        WHEN (SELECT SUM(amount) FROM sales WHERE employee_id = e.id) >
             (SELECT AVG(total) FROM (
                 SELECT SUM(amount) as total
                 FROM sales
                 GROUP BY employee_id
             ) t)
        THEN 'Above Average'
        ELSE 'Below Average'
    END as performance
FROM employees e;

发现问题了吗？计算个人销售额的子查询逻辑被重复书写了三次。一旦业务规则变化，你需要修改逻辑时，很容易遗漏其中一处，导致结果不一致和难以排查的 Bug。

现在，用 WITH 查询进行重构：

WITH
    employee_sales AS (
        SELECT employee_id, SUM(amount) as total_sales
        FROM sales
        GROUP BY employee_id
    ),
    avg_sales AS (
        SELECT AVG(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'
    END as performance
FROM employees e
JOIN employee_sales es ON e.id = es.employee_id
CROSS JOIN avg_sales avs;

重构后的代码逻辑层次分明：

• employee_sales CTE 清晰地定义了“个人销售额”这个中间结果。
• avg_sales CTE 基于第一个 CTE 的结果，计算“平均销售额”。
• 主查询只需简单地连接和引用这些已命名的结果集。

这就是 WITH 的核心价值：它不是为了减少代码字符数，而是为了提升代码的可读性、可维护性和可复用性。每个逻辑块都有了名字，想修改或调试哪一部分，就一目了然。如果你需要深入学习更多类似的数据库高级查询技巧，可以来云栈社区的数据库/中间件/技术栈板块与大家交流。

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

WITH 查询还有一个强大的“隐藏技能”：递归查询。这在处理具有层级或树状结构的数据时，堪称神器。例如组织架构、多级分类目录、评论的嵌套回复等场景，用传统 SQL 查询往往非常棘手，甚至无法实现。

假设有一个员工表，其中 manager_id 字段指向其上级领导的 ID：

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

现在，你想要查询出某个特定员工（例如 ID 为 1）的所有下属，包括直接下属和间接下属（下属的下属，以此类推）。用递归 CTE 可以轻松实现：

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

    UNION ALL

    -- 递归部分：找到向“上一层结果”中的人汇报的员工
    SELECT
        e.id,
        e.name,
        e.manager_id,
        s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

这个查询的执行过程像一个“广度优先搜索”：

1. 锚点：首先找到 ID=1 的员工。
2. 第一轮递归：找到所有 manager_id = 1 的员工（直接下属）。
3. 第二轮递归：以上一轮找到的员工 ID 为条件，继续找他们的下属。
4. 循环：如此反复，直到没有新的匹配结果，递归终止。

递归查询有两个关键部分，通过 UNION ALL 连接：

• 锚点成员：非递归查询，提供递归的初始结果集。
• 递归成员：引用 CTE 自身（这里是 subordinates），基于上一轮的结果生成新的数据行。

反向查询：找上级链

递归 CTE 同样可以轻松实现反向查询。例如，给定一个员工，找出从他的直属上级一直到 CEO 的完整管理链：

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

    UNION ALL

    -- 递归：向上查找，当前员工的经理成为下一轮查找的“员工”
    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
ORDER BY depth DESC;

这类技巧在构建权限系统、审批流或数据分析时非常实用，能够高效地处理复杂的关联关系。

一些需要注意的地方

在享受 WITH 查询带来的便利时，也需要了解其特性和潜在的性能考量。

性能问题：物化 (Materialization)
多数数据库（如早于 12 版本的 PostgreSQL）默认将 CTE 作为“内联视图”处理。这意味着如果一个 CTE 在主查询中被引用了多次，它可能会被重复执行多次。对于计算成本高昂的 CTE，这可能成为性能瓶颈。

现代数据库如 PostgreSQL 12+ 提供了 MATERIALIZED 选项，可以强制数据库将 CTE 的结果计算并临时存储一次，后续引用直接读取该结果：

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

递归深度限制
为了防止无限递归导致死循环，数据库通常会设置递归深度限制。例如，SQL Server 的默认最大递归深度是 100 层。超过此限制会报错。你可以通过查询提示来调整：

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

数据库支持情况
WITH 查询（尤其是递归 CTE）是相对较新的 SQL 标准。例如，MySQL 在 8.0 版本 才正式支持 CTE 和递归查询。如果你还在使用旧版本（如 5.7），可能需要考虑升级或寻找替代方案来实现层级查询。

写在最后

WITH 查询并不是什么高深莫测的黑科技，但它属于那种“一旦掌握就再也回不去”的高效工具。就像习惯了代码编辑器的智能提示后，就很难再接受纯文本编辑器一样。当你习惯使用 WITH 来组织复杂的查询逻辑后，再回头看那些层层嵌套、动辄数十行的子查询，会真切地感受到代码可读性带来的愉悦。

诚然，WITH 查询不一定总能提升执行速度（在某些情况下甚至可能因优化器策略而略慢），但它能极大地提升开发效率和团队协作的流畅度。在软件工程中，代码首先是写给人看的，清晰的逻辑和良好的可维护性，其长期价值往往远超那一点微妙的性能差异。

快速检查清单

• 查询超过 50 行，逻辑层层嵌套？ -> 考虑用 WITH 拆分成模块。
• 同一个子查询逻辑在语句中出现了两次以上？ -> 几乎肯定应该使用 WITH。
• 需要查询组织架构、分类树等层级关系？ -> 递归 CTE 是你的首选方案。
• CTE 计算成本高且被多次引用，担心性能？ -> 尝试使用 MATERIALIZED 关键字。

---