一、问题场景：为什么会重复 JOIN 同一个子查询

在写复杂 SQL 时，经常遇到这种需求：同一张表或同一个聚合子查询，要和主表关联多次，每次取不同维度的信息。比如：

• 订单表要关联"客户近30天消费总额"一次，又要关联"客户近90天消费总额"一次
• 用户表要关联"首次下单信息"一次，又要关联"最近一次下单信息"一次
• 商品表要关联"商品类别名称"一次，又要关联"商品父类别名称"一次

新手最容易写出的"能用但难读"的写法，就是把同一个子查询复制粘贴好几遍，每次 LEFT JOIN 一次。SQL 能跑，但又长又慢，还容易写错。

本文系统梳理这类问题的几种标准优化写法，从可读性和性能两个维度对比。

二、反面示例：重复书写同一个子查询

先看一个典型的反面教材：

SELECT 
    o.order_id,
    o.customer_id,
    t1.spend_30d,
    t2.spend_90d
FROM orders o
LEFT JOIN (
    SELECT 
        customer_id,
        SUM(order_amount) AS spend_30d
    FROM orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY customer_id
) t1 ON o.customer_id = t1.customer_id
LEFT JOIN (
    SELECT 
        customer_id,
        SUM(order_amount) AS spend_90d
    FROM orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY customer_id
) t2 ON o.customer_id = t2.customer_id;

这段 SQL 能跑，但有三个明显问题：

1. 子查询写了两遍，逻辑相同只是时间窗不同，维护时改一处忘另一处就会出 bug。
2. 性能差：数据库可能真的扫两遍订单表，大表上代价很高。
3. 可读性差：几百行 SQL 里嵌套两个几乎一样的子查询，排查问题很费劲。

三、优化方案一：用 CTE（WITH 子句）写一次

3.1 基本写法

最直接的优化是把重复的子查询提取到 WITH 子句里，定义一次，多处引用：

WITH spend_stats AS (
    SELECT 
        customer_id,
        SUM(CASE WHEN order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
                 THEN order_amount ELSE 0 END) AS spend_30d,
        SUM(CASE WHEN order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
                 THEN order_amount ELSE 0 END) AS spend_90d
    FROM orders
    GROUP BY customer_id
)
SELECT 
    o.order_id,
    o.customer_id,
    s.spend_30d,
    s.spend_90d
FROM orders o
LEFT JOIN spend_stats s ON o.customer_id = s.customer_id;

注意这里把"两次 JOIN"进一步合并成了"一次聚合 + 一次 JOIN"，用 CASE WHEN 在聚合时就把两个时间窗的金额算出来。这是最推荐的写法：扫一遍表，出两个指标。

3.2 CTE 的优势

• 只定义一次，逻辑集中，改起来只动一处。
• 语义清晰，CTE 名字就是这段逻辑的业务含义。
• 大多数现代数据库（MySQL 8+、PostgreSQL、Hive、Spark SQL）会自动把 CTE 优化成一次扫描。

四、优化方案二：一次 JOIN + 条件聚合

4.1 适用场景

如果要 LEFT JOIN 的不是"聚合结果"，而是"明细表中的不同时间点"（比如首次下单和最近一次下单），那 CTE 一次聚合可能不够，但仍可以用一次 JOIN 加条件聚合解决。

举个例子：要在用户表上同时取"首次下单日期"和"最近一次下单日期"：

-- 反面写法：两次 LEFT JOIN 同一张订单表
SELECT 
    u.user_id,
    first_order.first_date,
    last_order.last_date
FROM users u
LEFT JOIN (
    SELECT user_id, MIN(order_date) AS first_date
    FROM orders GROUP BY user_id
) first_order ON u.user_id = first_order.user_id
LEFT JOIN (
    SELECT user_id, MAX(order_date) AS last_date
    FROM orders GROUP BY user_id
) last_order ON u.user_id = last_order.user_id;

4.2 优化写法

其实这两个聚合完全可以一次算完：

SELECT 
    u.user_id,
    s.first_date,
    s.last_date
FROM users u
LEFT JOIN (
    SELECT 
        user_id,
        MIN(order_date) AS first_date,
        MAX(order_date) AS last_date
    FROM orders
    GROUP BY user_id
) s ON u.user_id = s.user_id;

一次扫描订单表，同时算出首次和最近日期。规律是：凡是按同一个 GROUP BY 字段聚合出多个指标的，都应该合并到一个子查询里，而不是写多个子查询分别 JOIN。

五、优化方案三：用窗口函数替代自连接

5.1 场景：同一张表关联不同"排名位置"的行

另一种常见情况是：想给每个用户同时取"第1单"、"第2单"、"最新一单"的信息。新手会写多次自连接。

-- 反面写法：三次 LEFT JOIN 同一张订单表
SELECT 
    u.user_id,
    o1.order_amount AS first_order_amount,
    o2.order_amount AS second_order_amount,
    o_latest.order_amount AS latest_order_amount
FROM users u
LEFT JOIN orders o1 ON ... -- 第1单
LEFT JOIN orders o2 ON ... -- 第2单
LEFT JOIN orders o_latest ON ... -- 最新单

5.2 优化写法：ROW_NUMBER + 条件聚合

WITH ranked_orders AS (
    SELECT 
        user_id,
        order_amount,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn_desc
    FROM orders
)
SELECT 
    u.user_id,
    MAX(CASE WHEN r.rn_asc = 1 THEN r.order_amount END) AS first_order_amount,
    MAX(CASE WHEN r.rn_asc = 2 THEN r.order_amount END) AS second_order_amount,
    MAX(CASE WHEN r.rn_desc = 1 THEN r.order_amount END) AS latest_order_amount
FROM users u
LEFT JOIN ranked_orders r ON u.user_id = r.user_id
GROUP BY u.user_id;

只扫一遍订单表、做一次窗口函数，用 CASE WHEN + MAX 把不同排名位置的行"透视"到同一行。比多次自连接高效很多，尤其是大表场景。

六、优化方案四：临时表 / 物化中间结果

6.1 适用场景

如果这个子查询特别重（比如要 JOIN 好几张表、做复杂聚合、跑好几分钟），且在同一个 SQL 里要被引用多次，CTE 可能在某些数据库里被反复执行（MySQL 5.x 的 CTE 就是"每次引用都展开"）。这时可以先把结果落到临时表：

-- 第一步：先把中间结果算出来存临时表
CREATE TEMPORARY TABLE tmp_customer_spend AS
SELECT 
    customer_id,
    SUM(CASE WHEN order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
             THEN order_amount ELSE 0 END) AS spend_30d,
    SUM(CASE WHEN order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
             THEN order_amount ELSE 0 END) AS spend_90d
FROM orders
GROUP BY customer_id;

-- 第二步：主查询直接 JOIN 临时表
SELECT 
    o.order_id,
    s.spend_30d,
    s.spend_90d
FROM orders o
LEFT JOIN tmp_customer_spend s ON o.customer_id = s.customer_id;

6.2 什么时候用临时表

• CTE 在你的数据库里不会被自动物化（老版本 MySQL）。
• 子查询本身计算很重，重复算代价高。
• 中间结果要在多个步骤里反复使用（比如后续还要再 JOIN 其他表）。

注意临时表会话结束自动销毁，不会污染正式库；用完记得 DROP TEMPORARY TABLE。

七、几种方案对比

八、关键注意事项

8.1 LEFT JOIN 后的行数膨胀问题

多次 LEFT JOIN 同一张"一对多"关系表时，容易出现行数膨胀。比如订单表关联客户表再关联订单明细表，中间多对多关系会让结果行数爆炸。解决思路是：先聚合再 JOIN，把子查询 GROUP BY 到主键粒度后再 LEFT JOIN 主表，避免一对多 JOIN 产生笛卡尔积。

8.2 CTE 在不同数据库里的行为差异

这点很容易踩坑：

• MySQL 8.0+：CTE 每次引用都可能重新执行（非物化），重复引用多次会扫多遍。
• PostgreSQL：CTE 默认是优化栅栏，但从 PG 12 开始可以被内联优化。
• Hive / Spark SQL：CTE 一般会被优化成一次扫描。

所以在 MySQL 8 里如果 CTE 被引用多次且很重，优先考虑临时表方案。

8.3 别为了"少写子查询"硬合并

合并的前提是：子查询的 GROUP BY 键相同、过滤条件能整合。如果两次 JOIN 的关联键完全不同（比如一次按 customer_id，一次按 product_id），硬合并反而会产生笛卡尔积。这种情况该写两个子查询还是要写两个，不要为了简洁而牺牲正确性。

8.4 优化完用 EXPLAIN 验证

写完之后用 EXPLAIN 看一下执行计划，确认订单表是不是真的只扫了一次。如果发现还是扫了多遍，可能是优化器没选中你预期的路径，这时再考虑临时表物化。

九、总结

同一个子查询被多次 LEFT JOIN，本质是两个问题：重复书写导致维护困难，重复扫描导致性能差。最优解几乎总是这两条：

1. 能合并成一次聚合就合并：把多个时间窗、多个排名位置的指标，用 CASE WHEN 在一次 GROUP BY 里算完，再 JOIN 一次主表。
2. 必须写多个子查询时，用 CTE 或临时表定义一次，不要把同一段 SQL 复制粘贴好几遍。

记住这条经验法则：凡是"按同一个维度分组、算多个指标"的需求，先在脑子里想"能不能一次 GROUP BY 出来"，再决定写几个 JOIN。绝大多数情况下，答案是能。

推荐学习书籍 《CDA一级教材》适合CDA一级考生备考，也适合业务及数据分析岗位的从业者提升自我。完整电子版已上线CDA网校，累计已有10万+在读~ !

免费加入阅读：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0