select id,sum(num) num from (select * from a union all select * from b) c group by id order by id
select a.id, a.num+isnull(b.num,0) from a left join b on a.id=b.id
select A.id,A.num+isnull(B.num,0) from A left Join B on A.id=B.id
select isnull(a.id,b.id) as id ,isnull(a.num,0)+isnull(b.num,0) as num from a full outer join b on a.id=b.id
if object_id('[A]') is not null drop table [A] go create table [A]([ID] int,[Num] int) insert [A] select 1,2 union all select 2,3 union all select 4,5 if object_id('[B]') is not null drop table [B] go create table [B]([ID] int,[Num] int) insert [B] select 1,3 union all select 2,4select a.id, a.num+isnull(b.num,0) as num from a left join b on a.id=b.id --测试结果: /* id num ----------- ----------- 1 5 2 7 4 5(所影响的行数为 3 行) */ 如果存在A表没有,B表有的数据,就像2楼说的,用full join
create table ta(ID int, Num int) go insert ta select 1 , 2 insert ta select 2 , 3 insert ta select 4 , 5 create table tb(ID int, Num int) go insert ta select 1 , 3 insert ta select 2 , 4 go select id,sum(num) num from (select id,num from ta union all select * from tb) c group by id order by iddrop table ta,tb /* id num ----------- ----------- 1 5 2 7 4 5(所影响的行数为 3 行) */
用isnull()函數,null與任何數進行運算結果都是null. isnull(字段A,0)=(case when 字段A is null then 0 else 字段A end)
create table #A ( ID int, NUm int ) insert into #A select 1,2 union all select 2,3 union all select 4,5create table #B ( ID int, NUm int ) insert into #B select 1,3 union all select 2,4select A.ID,sum(A.Num)+sum(isnull(B.Num,0)) 'NUm' from #A A full join #B B on A.ID=B.ID group by A.IDID NUm ----------- ----------- 1 5 2 7 4 5(3 行受影响)
修改下 select isnull(A.ID,B.ID) 'ID',sum(isnull(A.Num,0))+sum(isnull(B.Num,0)) 'NUm' from #A A full join #B B on A.ID=B.ID group by A.ID,B.ID order by A.ID,B.ID ID NUm ----------- ----------- 1 5 2 7 4 5(3 行受影响)
isnull正确,谢谢 LS的也是正确的
create table T1(ID int, Num int) go insert T1 select 1 , 2 union all select 2 , 3 union all select 4 , 5 create table T2(ID int, Num int) go insert T2 select 1 , 3 union all select 2 , 4 goselect id as ID,sum(num) as Num from (select * from T1 union all select * from T2) c group by ID order by ID/* ID Num ----------- ----------- 1 5 2 7 4 5(3 行受影响) */
(select * from a
union all
select * from b) c group by id order by id
a.id,
a.num+isnull(b.num,0)
from a
left join b on a.id=b.id
select A.id,A.num+isnull(B.num,0) from A left Join B on A.id=B.id
from a full outer join b on a.id=b.id
go
create table [A]([ID] int,[Num] int)
insert [A]
select 1,2 union all
select 2,3 union all
select 4,5
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[Num] int)
insert [B]
select 1,3 union all
select 2,4select
a.id,
a.num+isnull(b.num,0) as num
from a
left join b on a.id=b.id
--测试结果:
/*
id num
----------- -----------
1 5
2 7
4 5(所影响的行数为 3 行)
*/
如果存在A表没有,B表有的数据,就像2楼说的,用full join
go
insert ta select 1 , 2
insert ta select 2 , 3
insert ta select 4 , 5 create table tb(ID int, Num int)
go
insert ta select 1 , 3
insert ta select 2 , 4
go
select id,sum(num) num from
(select id,num from ta
union all
select * from tb) c group by id order by iddrop table ta,tb
/*
id num
----------- -----------
1 5
2 7
4 5(所影响的行数为 3 行)
*/
(
ID int,
NUm int
)
insert into #A select 1,2
union all select 2,3
union all select 4,5create table #B
(
ID int,
NUm int
)
insert into #B select 1,3
union all select 2,4select A.ID,sum(A.Num)+sum(isnull(B.Num,0)) 'NUm' from #A A full join #B B
on A.ID=B.ID group by A.IDID NUm
----------- -----------
1 5
2 7
4 5(3 行受影响)
select isnull(A.ID,B.ID) 'ID',sum(isnull(A.Num,0))+sum(isnull(B.Num,0)) 'NUm' from #A A full join #B B on A.ID=B.ID group by A.ID,B.ID order by A.ID,B.ID
ID NUm
----------- -----------
1 5
2 7
4 5(3 行受影响)
go
insert T1
select 1 , 2 union all
select 2 , 3 union all
select 4 , 5 create table T2(ID int, Num int)
go
insert T2
select 1 , 3 union all
select 2 , 4
goselect id as ID,sum(num) as Num
from (select * from T1
union all
select * from T2) c
group by ID
order by ID/*
ID Num
----------- -----------
1 5
2 7
4 5(3 行受影响)
*/