SQL查询优化：子查询转连接条件后sum结果不符求助

你的改写方案出现结果不一致的核心原因是左连接导致了笛卡尔积：如果同一个(purchase, year, item, period)分组下存在多条type='I'和type='A'的记录，左连接后两个子表的记录会两两组合，最终统计sum时会重复计算value值，导致结果偏大。

下面提供两种正确的优化方案，同时满足保留type为null的数据的需求：

方案1：条件聚合（推荐，简洁高效） #

直接在主查询中用CASE WHEN做条件统计，无需额外连接，性能最优：

select 
    purchase, 
    year,
    item,
    period,
    -- 统计type='I'的value总和，无数据则返回0（要返回null可去掉else 0）
    sum(case when type = 'I' then value else 0 end) as I,
    sum(case when type = 'A' then value else 0 end) as A
from tab_a
group by purchase, year, item, period

这里通过GROUP BY获取所有唯一的分组（包括type为null的分组），同时一次性完成两类统计，结果和原查询完全一致。

方案2：先聚合再连接 #

如果一定要用连接方式，需要先对type='I'和type='A'的数据分别做分组聚合，再和全部分组左连接，避免笛卡尔积：

select 
    g.purchase, 
    g.year,
    g.item,
    g.period,
    coalesce(I.sum_I, 0) as I,
    coalesce(A.sum_A, 0) as A
from (
    -- 先获取所有唯一分组，包含type为null的情况
    select distinct purchase, year, item, period 
    from tab_a
) g
left join (
    -- 预聚合type='I'的分组总和
    select purchase, year, item, period, sum(value) as sum_I
    from tab_a 
    where type = 'I'
    group by purchase, year, item, period
) I on g.purchase = I.purchase 
    and g.year = I.year 
    and g.item = I.item 
    and g.period = I.period
left join (
    -- 预聚合type='A'的分组总和
    select purchase, year, item, period, sum(value) as sum_A
    from tab_a 
    where type = 'A'
    group by purchase, year, item, period
) A on g.purchase = A.purchase 
    and g.year = A.year 
    and g.item = A.item 
    and g.period = A.period

这种方式先把每个类型的聚合结果计算完成，再进行连接，彻底避免了重复统计的问题。

内容的提问来源于stack exchange，提问作者Shru