tableAA
Name counts
AA 100
BB 50
CC 800
tableBB
Name counts
DD 100
BB 150
CC 200得到为
tableCC
Name counts
AA 100
BB 200
CC 1000
DD 100这两张表没有直接的关系,都是统计之后的结果再求和!
Name counts
AA 100
BB 50
CC 800
tableBB
Name counts
DD 100
BB 150
CC 200得到为
tableCC
Name counts
AA 100
BB 200
CC 1000
DD 100这两张表没有直接的关系,都是统计之后的结果再求和!
from
(select * from tableAA union all
select * from tableBB) t
group by Name
insert tableAA select 'AA' , 100
insert tableAA select 'BB' , 50
insert tableAA select 'CC' , 800
create table tableBB (Name varchar(10),counts int)
insert tableAA select 'DD', 100
insert tableAA select 'BB', 150
insert tableAA select 'CC', 200 select Name,sum(counts) counts
from
(select * from tableAA union all
select * from tableBB) t
group by Namedrop table tableAA,tableBBName counts
---------- -----------
AA 100
BB 200
CC 1000
DD 100(所影响的行数为 4 行)
tableAA
Name counts
AA 100
BB 50
CC 800
tableBB
Name counts
DD 100
BB 150
CC 200
*/
select A.Name,sum(A.counts) from (select Name from tableAA
union all
select Name from tableBB) A
group by A.Name
---------- -----------
AA 100
BB 200
CC 1000
DD 100
if not object_id('tba') is null
drop table tba
Go
Create table tba([Name] nvarchar(2),[counts] int)
Insert tba
select N'AA',100 union all
select N'BB',50 union all
select N'CC',800
Go
Select * from tba
--> (让你望见影子的墙)生成测试数据,时间:2009-03-14
if not object_id('tbb') is null
drop table tbb
Go
Create table tbb([Name] nvarchar(2),[counts] int)
Insert tbb
select N'DD',100 union all
select N'BB',150 union all
select N'CC',200
Go
Select * from tbb
select name ,sum(counts)
from (select * from tba union all select * from tbb) K
group by name
AA 100
BB 200
CC 1000
DD 100
select * from tableAA
union all
select * from tableBB) a group by a.Name order by a.Name
select * from tableAA
union all
select * from tableBB) a group by a.Name order by a.Name
select * from tableAA
union all
select * from tableBB) a group by a.Name order by a.Name正解
insert into #tableaa
select 'AA', 100 union all
select 'BB', 50 union all
select 'CC', 800 create table #tableBB ([name] varchar(10),counts int)
insert into #tablebb
select 'DD', 100 union all
select 'BB', 150 union all
select 'CC', 200 select max(a.[name]) as [name],sum(a.counts) as counts
from (select * from #tableaa union all select * from #tablebb) a
group by a.[name]/*
name counts
---------- -----------
AA 100
BB 200
CC 1000
DD 100(所影响的行数为 4 行)
*/