相同的表结构的数据怎么 用简单sql语句汇总

[yikun]

表test1 表test2
num1 num2 num1 num2
1 2 1 4
2 3 2 5
4 5 3 2

最好用一个sql语句 查询 得出
num1 num2
1 6
2 8
3 2
4 5
谢谢

[快乐_石头]

SELECT NUM1,SUM(NUM2) FROM (
SELECT * FROM TEST1
UNION ALL
SELECT * FROM TEST2)T GROUP BY NUM1

[chuifengde]

select num1,sum(num2) num2
from
(select * from test1
 union al
 select * from test2
)aa
group by num1

[百年树人]

select 
  num1,
  sum(num2) as num2
from
(
  select * from test1
  union all
  select * from test2
) t
group by num1

[快乐_石头]

CREATE TABLE TEST1(num1 INT, num2 INT)       
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 NUM1

DROP TABLE TEST1,TEST2
/*NUM1                    
----------- ----------- 
1           6
2           8
3           2
4           5

(影響 4 個資料列)

*/

[winter75]

[Quote=引用楼主 yikun 的帖子:]
表test1 表test2
num1 num2 num1 num2
1 2 1 4
2 3 2 5
4 5 3 2

最好用一个sql语句 查询 得出
num1 num2
1 6
2 8
3 2
4 5
谢谢
[/Quote]

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 行）

[lgx0914]

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