SQL多表连接分组后聚合值膨胀的原因咨询

问题原因 #

当你把TABLE1、TABLE2、TABLE3做内连接时，TABLE2中对应table1_id=1的只有1行，TABLE3中对应table1_id=1的有3行，两个表通过TABLE1关联后会产生笛卡尔积，最终连接后的结果集是3条重复的TABLE2数据：

对T2.earning求和时，相当于把这3行的10000累加，结果自然是30000，而非预期的10000。

解决办法 #

方法1：先聚合TABLE2再关联（通用方案）

先对TABLE2按table1_id完成聚合，再和其他表关联，从根源避免重复计算：

declare @TABLE1 table (table1_id int);
declare @TABLE2 table (table2_id int, table1_id int, earning money);
declare @TABLE3 table (table3_id int, table1_id int);

insert into @TABLE1 values (1);
insert into @TABLE2 values (49, 1, 10000);
insert into @TABLE3 values (991, 1);
insert into @TABLE3 values (992, 1);
insert into @TABLE3 values (993, 1);

select 
    T1.table1_id, T2_SUM.earning
from 
    @TABLE1 T1
inner join 
    (select table1_id, SUM(earning) as earning from @TABLE2 group by table1_id) T2_SUM 
    on T2_SUM.table1_id = T1.table1_id
inner join 
    @TABLE3 T3 on T3.table1_id = T1.table1_id
group by 
    T1.table1_id, T2_SUM.earning;

方法2：仅关联需要的表（如果不需要TABLE3数据）

如果你的查询不需要用到TABLE3的信息，直接关联TABLE1和TABLE2即可：

declare @TABLE1 table (table1_id int);
declare @TABLE2 table (table2_id int, table1_id int, earning money);
declare @TABLE3 table (table3_id int, table1_id int);

insert into @TABLE1 values (1);
insert into @TABLE2 values (49, 1, 10000);
insert into @TABLE3 values (991, 1);
insert into @TABLE3 values (992, 1);
insert into @TABLE3 values (993, 1);

select 
    T1.table1_id, SUM(T2.earning) as earning
from 
    @TABLE1 T1
inner join 
    @TABLE2 T2 on T2.table1_id = T1.table1_id
group by 
    T1.table1_id;

方法3：临时用DISTINCT（仅适用于单条数据场景）

如果TABLE2中每个table1_id只有1行数据，可以临时用SUM(DISTINCT)，但不推荐作为通用方案：

declare @TABLE1 table (table1_id int);
declare @TABLE2 table (table2_id int, table1_id int, earning money);
declare @TABLE3 table (table3_id int, table1_id int);

insert into @TABLE1 values (1);
insert into @TABLE2 values (49, 1, 10000);
insert into @TABLE3 values (991, 1);
insert into @TABLE3 values (992, 1);
insert into @TABLE3 values (993, 1);

select 
    T1.table1_id, SUM(DISTINCT T2.earning) as earning
from 
    @TABLE1 T1
inner join 
    @TABLE2 T2 on T2.table1_id = T1.table1_id
inner join 
    @TABLE3 T3 on T3.table1_id = T1.table1_id
group by 
    T1.table1_id;

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