select col1,col2,sum(col3) as col3 from a join b on a.col1=b.col1 and a.col2=b.col2 group by a.col1,a.col2
if object_id('[a]') is not null drop table [a] go create table [a]([a] int,[b] int,[c] int) insert [a] select 1,4,6 union all select 2,4,7if object_id('[b]') is not null drop table [b] go create table [b]([a] int,[b] int,[c] int) insert [b] select 1,4,3--查询 select a,b,sum(c) c from (select * from a union all select * from b) t group by a,b /* a b c ----------- ----------- ----------- 1 4 9 2 4 7(2 行受影响) */
select col1,col2,sum(col3) from ( select co1,col2,col3 from a union all select co1,col2,col3 from b) c group by col1,col2
楼主朋友,由于您没有给出字段的名字,那么我姑且将表中的3个字段依次命名成ID1,ID2,相加列。 生成表C的语句是: select A.ID1,A.ID2,(A.相加列 + B.相加列) as 相加列 from A,B where A.ID1 = B.ID1 and A.ID2 = B.ID2
declare @a table (a int,b int,c int) insert @a select 1,4,6 insert @a select 2,4,7 declare @b table (a int,b int,c int) insert @b select 1,4,3 select a.a,a.b,a.c+isnull(b.c,0)c from @a a left join b b on a.a=b.a and a.b=b.b /* a b c ----------- ----------- ----------- 1 4 9 2 4 7(影響 2 個資料列) */
if object_id('[a]') is not null drop table [a] go create table [a]([a] int,[b] int,[c] int) insert [a] select 1,4,6 union all select 2,4,7if object_id('[b]') is not null drop table [b] go create table [b]([a] int,[b] int,[c] int) insert [b] select 1,4,3select a.[a],b.[b],sum(a.[c])+sum(b.[c]) c from [a] a join b on a.[a]=b.[a] group by a.[a],b.[b]
declare @a table (a int,b int,c int) insert @a select 1,4,6 insert @a select 2,4,7 declare @b table (a int,b int,c int) insert @b select 1,4,3 select a.a,a.b,a.c+isnull(b.c,0)c from @a a left join b b on a.a=b.a and a.b=b.b /* a b c ----------- ----------- ----------- 1 4 9 2 4 7(影響 2 個資料列) */
declare @a table (a int,b int,c int) insert @a select 1,4,6 insert @a select 2,4,7 declare @b table (a int,b int,c int) insert @b select 1,4,3 select a.a,a.b,a.c+isnull(b.c,0)c from @a a left join b b on a.a=b.a and a.b=b.b /* a b c ----------- ----------- ----------- 1 4 9 2 4 7(影響 2 個資料列) */
if object_id('[a]') is not null drop table [a] go create table [a]([a] int,[b] int,[c] int) insert [a] select 1,4,6 union all select 2,4,7if object_id('[b]') is not null drop table [b] go create table [b]([a] int,[b] int,[c] int) insert [b] select 1,4,3select a.[a],b.[b],sum(a.[c])+sum(b.[c]) c from [a] a join b on a.[a]=b.[a] group by a.[a],b.[b] union all select a.[a],b.[b],a.[c] from [a] a join b on a.[a]<>b.[a] a b c ----------- ----------- ----------- 1 4 9 2 4 7(2 行受影响)
from a join b on a.col1=b.col1 and a.col2=b.col2
group by a.col1,a.col2
go
create table [a]([a] int,[b] int,[c] int)
insert [a] select 1,4,6
union all select 2,4,7if object_id('[b]') is not null drop table [b]
go
create table [b]([a] int,[b] int,[c] int)
insert [b] select 1,4,3--查询
select a,b,sum(c) c
from
(select * from a union all
select * from b) t
group by a,b
/*
a b c
----------- ----------- -----------
1 4 9
2 4 7(2 行受影响)
*/
select co1,col2,col3 from a
union all
select co1,col2,col3 from b) c
group by col1,col2
生成表C的语句是:
select A.ID1,A.ID2,(A.相加列 + B.相加列) as 相加列
from A,B
where A.ID1 = B.ID1 and A.ID2 = B.ID2
insert @a select 1,4,6
insert @a select 2,4,7
declare @b table (a int,b int,c int)
insert @b select 1,4,3
select a.a,a.b,a.c+isnull(b.c,0)c from @a a
left join b b on a.a=b.a and a.b=b.b
/*
a b c
----------- ----------- -----------
1 4 9
2 4 7(影響 2 個資料列)
*/
go
create table [a]([a] int,[b] int,[c] int)
insert [a] select 1,4,6
union all select 2,4,7if object_id('[b]') is not null drop table [b]
go
create table [b]([a] int,[b] int,[c] int)
insert [b] select 1,4,3select a.[a],b.[b],sum(a.[c])+sum(b.[c]) c from [a] a join b
on a.[a]=b.[a] group by a.[a],b.[b]
insert @a select 1,4,6
insert @a select 2,4,7
declare @b table (a int,b int,c int)
insert @b select 1,4,3
select a.a,a.b,a.c+isnull(b.c,0)c from @a a
left join b b on a.a=b.a and a.b=b.b
/*
a b c
----------- ----------- -----------
1 4 9
2 4 7(影響 2 個資料列)
*/
insert @a select 1,4,6
insert @a select 2,4,7
declare @b table (a int,b int,c int)
insert @b select 1,4,3
select a.a,a.b,a.c+isnull(b.c,0)c from @a a
left join b b on a.a=b.a and a.b=b.b
/*
a b c
----------- ----------- -----------
1 4 9
2 4 7(影響 2 個資料列)
*/
go
create table [a]([a] int,[b] int,[c] int)
insert [a] select 1,4,6
union all select 2,4,7if object_id('[b]') is not null drop table [b]
go
create table [b]([a] int,[b] int,[c] int)
insert [b] select 1,4,3select a.[a],b.[b],sum(a.[c])+sum(b.[c]) c from [a] a join b
on a.[a]=b.[a] group by a.[a],b.[b]
union all
select a.[a],b.[b],a.[c] from [a] a join b
on a.[a]<>b.[a]
a b c
----------- ----------- -----------
1 4 9
2 4 7(2 行受影响)