left join + group by 求sum

Question

table1:id    type1    type2    type3    sum1
1      a        a        a        10
2      a        a        a        20
3      b        b        b        15
4      a        a        a        13
5      c        c        c        22
table2:id    type1    type2    type3    sum2
1      a        a        a        8
2      a        a        a        11
3      b        b        b        9
4      b        b        b        4
5      a        a        a        3我要得到像这样：
id    type1    type2    type3    sum1    sum2
1      a        a        a        33      22
2      b        b        b        15      13
就是两张表type完全一样的group一下，以table1为准(left join)，然后分别计算sum1和sum2的总和，求最优写法

Answer 1

select m.type1 ,
       m.type2 ,
       m.type3 ,
       sum(m.sum1) sum1,
       (select sum(sum2) from table2 n where n.type1 = m.type1 and n.type2 = m.type2 and n.type3 = m.type3) sum2
from table1 m
group by m.type1,m.type2,m.type3

Answer 2

create table table1(id int,type1 varchar(10),type2 varchar(10),type3 varchar(10),sum1 int)
insert into table1 values(1 ,'a', 'a', 'a', 10)
insert into table1 values(2 ,'a', 'a', 'a', 20)
insert into table1 values(3 ,'b', 'b', 'b', 15)
insert into table1 values(4 ,'a', 'a', 'a', 13)
insert into table1 values(5 ,'c', 'c', 'c', 22)
create table table2(id int,type1 varchar(10),type2 varchar(10),type3 varchar(10),sum2 int)
insert into table2 values(1 ,'a', 'a', 'a', 8)
insert into table2 values(2 ,'a', 'a', 'a', 11)
insert into table2 values(3 ,'b', 'b', 'b', 9)
insert into table2 values(4 ,'b', 'b', 'b', 4)
insert into table2 values(5 ,'a', 'a', 'a', 3)
goselect m.type1 ,
       m.type2 ,
       m.type3 ,
       sum(m.sum1) sum1,
       isnull((select sum(sum2) from table2 n where n.type1 = m.type1 and n.type2 = m.type2 and n.type3 = m.type3),0) sum2
from table1 m
group by m.type1,m.type2,m.type3drop table table1 , table2/*
type1      type2      type3      sum1        sum2        
---------- ---------- ---------- ----------- ----------- 
a          a          a          43          22
b          b          b          15          13
c          c          c          22          0（所影响的行数为 3 行）
*/

Answer 3

不是吧，只能这么写么？不能用left join么

Answer 4

你两表是多对多的关系，如果要left join，需要使用子查询，其效率可能不如我前面那句。create table table1(id int,type1 varchar(10),type2 varchar(10),type3 varchar(10),sum1 int)
insert into table1 values(1 ,'a', 'a', 'a', 10)
insert into table1 values(2 ,'a', 'a', 'a', 20)
insert into table1 values(3 ,'b', 'b', 'b', 15)
insert into table1 values(4 ,'a', 'a', 'a', 13)
insert into table1 values(5 ,'c', 'c', 'c', 22)
create table table2(id int,type1 varchar(10),type2 varchar(10),type3 varchar(10),sum2 int)
insert into table2 values(1 ,'a', 'a', 'a', 8)
insert into table2 values(2 ,'a', 'a', 'a', 11)
insert into table2 values(3 ,'b', 'b', 'b', 9)
insert into table2 values(4 ,'b', 'b', 'b', 4)
insert into table2 values(5 ,'a', 'a', 'a', 3)
go--1
select m.* , isnull(n.sum2,0) sum2 from
(select type1,type2,type3,sum(sum1) sum1 from table1 group by type1,type2,type3) m
left join
(select type1,type2,type3,sum(sum2) sum2 from table2 group by type1,type2,type3) n
on m.type1 = n.type1 and m.type2 = n.type2 and m.type3 = n.type3--2
select m.type1 ,
       m.type2 ,
       m.type3 ,
       sum(m.sum1) sum1,
       isnull((select sum(sum2) from table2 n where n.type1 = m.type1 and n.type2 = m.type2 and n.type3 = m.type3),0) sum2
from table1 m
group by m.type1,m.type2,m.type3drop table table1 , table2/*
type1      type2      type3      sum1        sum2        
---------- ---------- ---------- ----------- ----------- 
a          a          a          43          22
b          b          b          15          13
c          c          c          22          0（所影响的行数为 3 行）
*/