22,301
社区成员




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.type3
drop table table1 , table2
/*
type1 type2 type3 sum1 sum2
---------- ---------- ---------- ----------- -----------
a a a 43 22
b b b 15 13
c c c 22 0
(所影响的行数为 3 行)
*/
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
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.type3
drop table table1 , table2
/*
type1 type2 type3 sum1 sum2
---------- ---------- ---------- ----------- -----------
a a a 43 22
b b b 15 13
c c c 22 0
(所影响的行数为 3 行)
*/
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