select m.num1,sum(m.num2) from (select num1,num2 from test1 union select num1,num2 from test2) m group by m.num1
---测试数据--- if object_id('[test1]') is not null drop table [test1] go create table [test1]([num1] int,[num2] int) insert [test1] select 1,2 union all select 2,3 union all select 4,5 if object_id('[test2]') is not null drop table [test2] go create table [test2]([num1] int,[num2] int) insert [test2] select 1,4 union all select 2,5 union all select 3,2
---查询--- select num1, sum(num2) as num2 from ( select * from test1 union all select * from test2 ) t group by num1---结果--- num1 num2 ----------- ----------- 1 6 2 8 3 2 4 5(所影响的行数为 4 行)
create table test1 (num1 int,num2 int) insert into test1 values(1,2) insert into test1 values(2,3) insert into test1 values(4,5)create table test2 (num1 int,num2 int) insert into test2 values(1,4) insert into test2 values(2,5) insert into test2 values(3,2)select isnull(test1.num1,test2.num1),isnull(isnull(sum(test2.num2)+sum(test1.num2),test2.num2),test1.num2) from test1 full join test2 on test1.num1=test2.num1 group by test1.num1,test2.num1,test2.num2,test1.num2
SELECT * FROM TEST1
UNION ALL
SELECT * FROM TEST2)T GROUP BY NUM1
from
(select * from test1
union al
select * from test2
)aa
group by num1
num1,
sum(num2) as num2
from
(
select * from test1
union all
select * from test2
) t
group by num1
INSERT TEST1 SELECT 1 , 2
INSERT TEST1 SELECT 2 ,3
INSERT TEST1 SELECT 4 ,5
CREATE TABLE TEST2(num1 INT, num2 INT)
INSERT TEST2 SELECT 1 , 4
INSERT TEST2 SELECT 2 , 5
INSERT TEST2 SELECT 3, 2
SELECT NUM1,SUM(NUM2) FROM (
SELECT * FROM TEST1
UNION ALL
SELECT * FROM TEST2)T GROUP BY NUM1DROP TABLE TEST1,TEST2
/*NUM1
----------- -----------
1 6
2 8
3 2
4 5(影響 4 個資料列)*/
(select num1,num2 from test1
union
select num1,num2 from test2) m
group by m.num1
if object_id('[test1]') is not null drop table [test1]
go
create table [test1]([num1] int,[num2] int)
insert [test1]
select 1,2 union all
select 2,3 union all
select 4,5
if object_id('[test2]') is not null drop table [test2]
go
create table [test2]([num1] int,[num2] int)
insert [test2]
select 1,4 union all
select 2,5 union all
select 3,2
---查询---
select
num1,
sum(num2) as num2
from
(
select * from test1
union all
select * from test2
) t
group by num1---结果---
num1 num2
----------- -----------
1 6
2 8
3 2
4 5(所影响的行数为 4 行)
insert into test1 values(1,2)
insert into test1 values(2,3)
insert into test1 values(4,5)create table test2 (num1 int,num2 int)
insert into test2 values(1,4)
insert into test2 values(2,5)
insert into test2 values(3,2)select isnull(test1.num1,test2.num1),isnull(isnull(sum(test2.num2)+sum(test1.num2),test2.num2),test1.num2)
from test1 full join test2 on test1.num1=test2.num1
group by test1.num1,test2.num1,test2.num2,test1.num2