34,590
社区成员
发帖
与我相关
我的任务
分享
--如果取两表不匹配的col1,考虑用全连接full join 或左连接,右连接.
create table tb1(col1 varchar(10), col2 int)
insert into tb1 values('A', 100)
insert into tb1 values('B', 200)
insert into tb1 values('A', 300)
insert into tb1 values('C', 400)
insert into tb1 values('B', 500)
create table tb2(col1 varchar(10), col2 varchar(10), col3 int)
insert into tb2 values('A', 'a1', 10 )
insert into tb2 values('A', 'a2', 20 )
insert into tb2 values('A', 'a3', 20 )
insert into tb2 values('B', 'b1', 40 )
go
select isnull(m.col1,n.col1) col1 , isnull(n.col3,0) col3 , isnull(m.col2,0) col2 from
(select col1 , col2 = sum(col2) from tb1 group by col1) m full join
(select col1 , col3 = sum(col3) from tb2 group by col1) n
on m.col1 = n.col1
drop table tb1,tb2
/*
col1 col3 col2
---------- ----------- -----------
A 50 400
B 40 700
C 0 400
(所影响的行数为 3 行)
*/
create table tb1(col1 varchar(10), col2 int)
insert into tb1 values('A', 100)
insert into tb1 values('B', 200)
insert into tb1 values('A', 300)
insert into tb1 values('C', 400)
insert into tb1 values('B', 500)
create table tb2(col1 varchar(10), col2 varchar(10), col3 int)
insert into tb2 values('A', 'a1', 10 )
insert into tb2 values('A', 'a2', 20 )
insert into tb2 values('A', 'a3', 20 )
insert into tb2 values('B', 'b1', 40 )
go
select m.col1 , n.col3 , m.col2 from
(select col1 , col2 = sum(col2) from tb1 group by col1) m,
(select col1 , col3 = sum(col3) from tb2 group by col1) n
where m.col1 = n.col1
drop table tb1,tb2
/*
col1 col3 col2
---------- ----------- -----------
A 50 400
B 40 700
(所影响的行数为 2 行)
*/
select a.Col1,T_1,T_2
from
(select Col1 ,sum(col2) as T_1 from table1 group by col1) a
inner join
(select Col1,sum(col3) as T_2 from table2 group by col1) b
on a.Col1=b.Col1
select m.col1 , n.col3 , m.col2 from
(select col1 , col2 = sum(col2) from tb1 group by col1) m,
(select col1 , col3 = sum(col3) from tb2 group by col1) n
where m.col1 = n.col1
create table tb1(col1 varchar(10), col2 int)
insert into tb1 values('A', 100)
insert into tb1 values('B', 200)
insert into tb1 values('A', 300)
insert into tb1 values('C', 400)
insert into tb1 values('B', 500)
create table tb2(col1 varchar(10), col2 varchar(10), col3 int)
insert into tb2 values('A', 'a1', 10 )
insert into tb2 values('A', 'a2', 20 )
insert into tb2 values('A', 'a3', 20 )
insert into tb2 values('B', 'b1', 40 )
go
select
a.col1,
[col2]=(select sum(col3) from tb2 where col1=a.col1),
[col3]=(select sum(col2) from tb1 where col1=a.col1)
from
tb1 a,tb2 b
where
a.col1=b.col1
group by a.col1
col1 col2 col3
---------- ----------- -----------
A 50 400
B 40 700
(所影响的行数为 2 行)