告别“SQL屎山”：如何用WITH（CTE）优雅地重构你的超长脚本

一、 引子：我们遭遇的“SQL屎山”

先来看一个业务系统中经典的复杂查询场景（为简化示例，已做抽象）：

需求：统计每个部门业绩最好的优秀员工（定义为：业绩超过部门平均业绩150%的员工）及其业绩占比。

传统的写法，可能会陷入深深的子查询嵌套地狱：

SELECT 
    d.dept_name,
    e.emp_name,
    e.performance,
    (e.performance / dept_avg.avg_perf) AS performance_ratio
FROM 
    employees e
JOIN 
    departments d ON e.dept_id = d.dept_id
JOIN 
    (SELECT 
         dept_id, 
         AVG(performance) AS avg_perf 
     FROM 
         employees 
     GROUP BY 
         dept_id) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE 
    e.performance > dept_avg.avg_perf * 1.5
ORDER BY 
    d.dept_name, performance_ratio DESC;

这还只是个简单的例子！现实中，逻辑会更复杂，FROM和WHERE后面可能跟着五六层子查询，SELECT列表里也全是带聚合函数的子查询。这种代码的缺点显而易见：

1. 可读性差：逻辑被切割成碎片，散落在各处，难以一眼看清业务逻辑。

2. 维护地狱：想修改一个计算逻辑？你得在好几层嵌套里找到它，一不小心就改错。

3. 重复代码：相同的子查询可能在多个地方出现，违反了DRY（Don‘t Repeat Yourself）原则。

4. 中间表泛滥：为了调试和分步计算，很多人会选择CREATE TEMP TABLE ...，产生大量需要管理的中间表。

二、 神器登场：什么是WITH（CTE）？

公共表表达式（Common Table Expression， CTE），使用WITH关键字开头，它可以定义一个临时的命名结果集，这个结果集只在本次查询中有效。你可以把它理解为查询中的“变量”，先定义，后使用。

基本语法：

WITH cte_name1 AS (
    -- 你的第一个查询
    SELECT ...
),
cte_name2 AS (
    -- 可以引用cte_name1和其他表
    SELECT ... FROM cte_name1 JOIN ...
)
-- 最终的主查询
SELECT * FROM cte_name2 WHERE ...;

三、 化腐朽为神奇：用CTE重构“屎山”

现在，让我们用CTE来重构开头的那个复杂查询。看看魔法是如何发生的。

WITH 
-- CTE1: 计算每个部门的平均业绩
dept_avg_performance AS (
    SELECT 
        dept_id, 
        AVG(performance) AS avg_perf 
    FROM 
        employees 
    GROUP BY 
        dept_id
),
-- CTE2: 找出哪些员工超过了部门平均的150%
outstanding_employees AS (
    SELECT 
        e.emp_id,
        e.emp_name,
        e.dept_id,
        e.performance,
        dap.avg_perf,
        -- 计算业绩比例
        (e.performance / dap.avg_perf) AS performance_ratio
    FROM 
        employees e
    JOIN 
        dept_avg_performance dap ON e.dept_id = dap.dept_id
    WHERE 
        e.performance > dap.avg_perf * 1.5
)
-- 最终主查询：关联部门表，获取更详细的信息并排序
SELECT 
    d.dept_name,
    oe.emp_name,
    oe.performance,
    oe.avg_perf AS department_avg_performance,
    oe.performance_ratio
FROM 
    outstanding_employees oe
JOIN 
    departments d ON oe.dept_id = d.dept_id
ORDER BY 
    d.dept_name, oe.performance_ratio DESC;

感受一下这前后的巨大差异！

优势分析：

1. ⛳ 清晰的逻辑流：代码变成了“自顶向下”的结构。就像写文章先列大纲：

   • 第一步，dept_avg_performance：计算部门平均。

   • 第二步，outstanding_employees：基于第一步的结果，筛选优秀员工并计算比率。

   • 最后，主查询：只是做一些简单的关联和展示。
     任何一个人都能在30秒内看懂整个业务逻辑。

2. 🚫 告别中间表：整个过程没有使用一句CREATE TEMP TABLE，省去了权限申请、磁盘I/O和管理命名的工作。CTE是纯逻辑上的临时视图。

3. ♻️ 强大的可复用性：如果dept_avg_performance这个结果需要在多个地方被引用，我们只需在CTE中定义一次，然后就可以在各个后续CTE和主查询中随意使用。避免了代码重复。

四、 进阶玩法：CTE的递归威力

CTE还有一个独门绝技：递归查询。这是临时表和子查询难以实现的。常用于处理树形或层级数据。

经典例子：查询一个部门的所有下级部门（假设有dept_id和parent_dept_id字段）

WITH RECURSIVE sub_departments AS (
    -- 锚点成员：先找到起点（例如，总部ID为1）
    SELECT dept_id, dept_name, parent_dept_id, 1 AS level
    FROM departments
    WHERE dept_id = 1
    UNION ALL
    -- 递归成员：基于锚点成员，不断向下查找
    SELECT d.dept_id, d.dept_name, d.parent_dept_id, sd.level + 1
    FROM departments d
    INNER JOIN sub_departments sd ON d.parent_dept_id = sd.dept_id
)
SELECT * FROM sub_departments;

这个查询会从ID为1的部门开始，逐级向下查找所有子部门，并计算出层级关系。这种能力在处理组织架构、菜单树、评论链等场景时无可替代。

五、 注意事项与最佳实践

1. 性能考量：CTE≠性能银弹。在大多数现代数据库（如PostgreSQL, SQL Server, Oracle）中，CTE默认是非物化的，它更像一个视图定义，查询优化器会将其与主查询一起优化，性能与写子查询相当。一定要养成使用EXPLAIN分析执行计划的习惯。

2. 物化CTE：有些数据库（如MySQL 8.0+）支持MATERIALIZED关键字，可以强制物化CTE（将其结果临时存储），在特定场景下可能提升性能。但不要滥用。

3. 命名要有意义：CTE的名字dept_avg_performance远比temp1、cte2要好，这是保证可读性的关键。

六、 总结

公共表表达式（CTE）是SQL提供给我们的强大武器，它完美地解决了复杂查询的可读性和维护性问题。通过将查询模块化，我们能够：

• 写出像故事一样流畅的SQL，逻辑清晰，易于理解和维护。

• 摆脱大量物理临时表的束缚，让查询更轻盈。

• 实现递归等高级功能，轻松处理层级数据。

下次当你又要动手创建临时表或者写出多层嵌套时，不妨先停下来想一想：“能不能用WITH来优雅地解决？” 相信我，一旦你用上它，就再也回不去了。

讨论：你在工作中还遇到过哪些用CTE巧妙解决的案例？欢迎在评论区分享交流