select c,isnull(cnt,0) from t2 a left join (select a,sum(b)cnt from t1 group by a)b on a.a=b.a
select c,sum(b) as b from (select isnull(t1.b,0) as b,t2.c from t1 right join t2 on t1.a = t2.a) as t group by c
select c,isnull(cnt,0) from t2 a left join (select a,sum(b)cnt from t1 group by a)b on a.a=b.a
t1:a, b t2:c a A 1 X A B 5 Y B A 2 Z C 结果: X 3 Y 5 Z 0select t2.c , isnull(sum(t1.b),0) b from t2 left join t1 on t2.a = t1.a
create table t1(a varchar(10) , b int) insert into t1 values('A', 1) insert into t1 values('B', 5) insert into t1 values('A', 2) create table t2(c varchar(10) , a varchar(10)) insert into t2 values('X', 'A') insert into t2 values('Y', 'B') insert into t2 values('Z', 'C') goselect t2.c , isnull(sum(t1.b),0) b from t2 left join t1 on t2.a = t1.a group by t2.cdrop table t1 , t2/* c b ---------- ----------- X 3 Y 5 Z 0 (所影响的行数为 3 行) */
create table t1 (a char(10),b int) insert into t1 select 'A',1 union all select 'B',5 union all select 'A',2 create table t2 (c char(10),a char(10)) insert into t2 select 'X','A' union all select 'Y','B' union all select 'z','C' ------------ select sum(isnull(dbo.t1.b ,0)) as newb,dbo.t2.c from dbo.t1 full outer join dbo.t2 on dbo.t1.a = dbo.t2.a group by dbo.t2.c ------------- 3 X 5 Y 0 z
select c,(select isnull(sum(b),0) from t1 where a=t2.a) as cnt from t2
select c,isnull(cnt,0) from t2 a left join
(select a,sum(b)cnt from t1 group by a)b
on a.a=b.a
select c,sum(b) as b from
(select isnull(t1.b,0) as b,t2.c from t1
right join t2 on t1.a = t2.a) as t
group by c
select c,isnull(cnt,0) from t2 a left join
(select a,sum(b)cnt from t1 group by a)b
on a.a=b.a
A 1 X A
B 5 Y B
A 2 Z C 结果:
X 3
Y 5
Z 0select t2.c , isnull(sum(t1.b),0) b
from t2 left join t1
on t2.a = t1.a
insert into t1 values('A', 1)
insert into t1 values('B', 5)
insert into t1 values('A', 2)
create table t2(c varchar(10) , a varchar(10))
insert into t2 values('X', 'A')
insert into t2 values('Y', 'B')
insert into t2 values('Z', 'C')
goselect t2.c , isnull(sum(t1.b),0) b
from t2 left join t1
on t2.a = t1.a
group by t2.cdrop table t1 , t2/*
c b
---------- -----------
X 3
Y 5
Z 0
(所影响的行数为 3 行)
*/
insert into t1
select 'A',1
union all
select 'B',5
union all
select 'A',2
create table t2 (c char(10),a char(10))
insert into t2
select 'X','A'
union all
select 'Y','B'
union all
select 'z','C'
------------
select sum(isnull(dbo.t1.b ,0)) as newb,dbo.t2.c
from dbo.t1 full outer join dbo.t2
on dbo.t1.a = dbo.t2.a
group by dbo.t2.c
-------------
3 X
5 Y
0 z
select c,(select isnull(sum(b),0) from t1 where a=t2.a) as cnt from t2