现在有两表,格式(列)完全一样:create table testA(uName varchar,aNum int,bNum int,cNum int)
insert testA select 'A',10,10,10
union all select 'B',20,20,20
union all select 'C',30,30,30create table testB(uName varchar,aNum int,bNum int,cNum int)
insert testB select 'A',40,40,40
union all select 'C',50,50,50
union all select 'D',60,60,60表testA的数据:
uName aNum bNum cNum
----- ----------- ----------- -----------
A 10 10 10
B 20 20 20
C 30 30 30表testB的数据:
uName aNum bNum cNum
----- ----------- ----------- -----------
A 40 40 40
C 50 50 50
D 60 60 0现在需要对2表数据,根据uName列进行整合为一张表。要求是:求A表和B表的并集(对A表和B表都存在的数据,进行相加)。期望结果如下:
uName aNum bNum cNum
----- ----------- ----------- -----------
A 50 50 50
B 20 20 20
C 80 80 80
D 60 60 0谢谢大家,在线等待。
insert testA select 'A',10,10,10
union all select 'B',20,20,20
union all select 'C',30,30,30create table testB(uName varchar,aNum int,bNum int,cNum int)
insert testB select 'A',40,40,40
union all select 'C',50,50,50
union all select 'D',60,60,60表testA的数据:
uName aNum bNum cNum
----- ----------- ----------- -----------
A 10 10 10
B 20 20 20
C 30 30 30表testB的数据:
uName aNum bNum cNum
----- ----------- ----------- -----------
A 40 40 40
C 50 50 50
D 60 60 0现在需要对2表数据,根据uName列进行整合为一张表。要求是:求A表和B表的并集(对A表和B表都存在的数据,进行相加)。期望结果如下:
uName aNum bNum cNum
----- ----------- ----------- -----------
A 50 50 50
B 20 20 20
C 80 80 80
D 60 60 0谢谢大家,在线等待。
解决方案 »
- 请教关于单个表查询, 对于某条件重复的数据,只提取随机一条的问题.(简单的SQL查询语句)
- 关于事务的问题
- 【请求帮助】求SQL语句,关于判断范围的问题,结贴就给分
- 如果通过sql语句判断一个字段是否非空字段?
- 如何删除一次性一个数据库的所有外键,主键和约束(在线等)
- 许可证
- SQL2005数据库格式与SQL2000数据库格式之间的转换问题?
- 在过程中日期2006-05-21 17:15:43 如何只要日期2006-05-21,不用Convert(varchar(20),mydate,102)
- 数据库备份和恢复的问题
- SQL-Sever 2000的数据库能否做成独立的文件(不安装SQL也能被程序调用?)
- 请问怎样做才能改变计数器SQLServer:SSIS Pipeline - Buffers spooled的值
- 关于触发器中的计算的,求助!
from
(select * from testA
union all
select * from testB
)A
group by uName
from (
select * from testA
union all
select * from testA
) as t
group by uName
insert testA select 'A',10,10,10
union all select 'B',20,20,20
union all select 'C',30,30,30create table testB(uName varchar,aNum int,bNum int,cNum int)
insert testB select 'A',40,40,40
union all select 'C',50,50,50
union all select 'D',60,60,60select uName,aNum=sum(aNum),bNum=sum(bNum),cNum =sum(cNum)
from
(select * from testA
union all
select * from testB
)A
group by uName--drop table testA,testB
--结果
uName,aNum,bNum,cNum
------------------------
A 50 50 50
B 20 20 20
C 80 80 80
D 60 60 60
SELECT UserName,SUM(aNum),SUM(bNum),
SUM(cNum) FROM
(SELECT * FROM testA
UNION ALL
SELECT * FROM testB) a GROUP BY UserName
isnull(a.aNum,0)+isnull(b.aNum,0) as aNum,
isnull(a.bNum,0)+isnull(b.bNum,0) as bNum,
isnull(a.cNum,0)+isnull(b.cNum,0) as cNum
from testA a full join testB b
on a.uName=b.uName
select uName,sum(aNum) as aNum,sum(bNum) as bNum,sum(cNum) as cNum from (
select * from testA
Union
select * from testB) b group by uName
uName aNum bNum cNum
----- ----------- ----------- -----------
A -30 -30 -30
B 20 20 20
C -20 -20 -20
D -60 -60 0