同时统计(sum)主表字段和明细表字段,当直接用主表 left join 明细表会导致由于主表的统计条数被明细表改变而出现数据不准
select
t1.ID,
sum(t1.a),
sum(t1.b),
sum(t2.c),
sum(t2.d)
from t1
left join t2 on t1.ID=t2.PID
where XXXXXX
改为
-- 创建中间表单独计算b表需要的数据
select
t1.ID,
sum(t2.c), -- 明细表维度求和
sum(t2.d)
INTO Test_temp
from t1
left join t2 on t1.ID=t2.PID
where XXXXXX
-- 把关联明细表改为关联中间表
select
t1.ID,
sum(t1.a),
sum(t1.b),
sum(Test_temp.c), -- 主表维度再求和一次
sum(Test_temp.d)
from t1
left join Test_temp on t1.ID=Test_temp.ID
where XXXXXX