SELECT M,SUM(a1) as 'a1+b1',SUM(a2) as 'a2+b2' FROM (SELECT DatePart(month,data)AS M,a1,a2 FROM A UNION SELECT DatePart(month,data) AS M,b1 as a1,b2 as a2 FROM B) A GROUP BY M
select [year]=isnull(t1.[year],t2.[year]), [month]=isnull( t1.[month],t2.[month]), [a1+b1]=isnull(a1,0)+isnull(b1,0), [a2+b2]=isnull(a2,0)+isnull(b2,0) from ( select [year]=year(data), [month]=month(data ), [a1]=sum(a1), [a2]=sum(a2) from a group by month(data ),year(data) )t1 full join --full join 连接 ( select [year]=year(data), [month]=month(data ), [b1]=sum(b1), [b2]=sum(b2) from b group by month(data ),year(data) )t2 on t1.[year]=t2.[year] and t1.[month]=t2.[month]
如果有跨年时要加上year作为条件
SELECT DatePart(month,data)AS Month,SUM(a1) as 'a1+b1',SUM(a2) as 'a2+b2' FROM (SELECT data,a1,a2 FROM A UNION SELECT data,b1 as a1,b2 as a2 FROM B) aa GROUP BY DatePart(month,data)
用临时表。效率高 declare @a table(data datetime,a1 int,a2 int) insert into @a select '2007-01-01',20,30 insert into @a select '2007-01-02',10,40 insert into @a select '2007-02-03',30,10 insert into @a select '2007-08-05',10,20declare @b table(data datetime,b1 int,b2 int) insert into @b select '2007-01-01',30,10 insert into @b select '2007-01-02',40,50 insert into @b select '2007-02-03',10,10 insert into @b select '2007-03-05',10,20select * into #temp from @a insert into #temp select * from @b select datepart(mm,data) data,sum(a1) a1,sum(a2) a2 from #temp group by datepart(mm,data)
[year]=isnull(t1.[year],t2.[year]),
[month]=isnull( t1.[month],t2.[month]),
[a1+b1]=isnull(a1,0)+isnull(b1,0),
[a2+b2]=isnull(a2,0)+isnull(b2,0)
from
(
select
[year]=year(data),
[month]=month(data ),
[a1]=sum(a1),
[a2]=sum(a2)
from
a
group by month(data ),year(data)
)t1
full join --full join 连接
(
select
[year]=year(data),
[month]=month(data ),
[b1]=sum(b1),
[b2]=sum(b2)
from
b
group by month(data ),year(data)
)t2
on t1.[year]=t2.[year] and t1.[month]=t2.[month]
(SELECT data,a1,a2 FROM A
UNION
SELECT data,b1 as a1,b2 as a2 FROM B) aa
GROUP BY DatePart(month,data)
用临时表。效率高
declare @a table(data datetime,a1 int,a2 int)
insert into @a select '2007-01-01',20,30
insert into @a select '2007-01-02',10,40
insert into @a select '2007-02-03',30,10
insert into @a select '2007-08-05',10,20declare @b table(data datetime,b1 int,b2 int)
insert into @b select '2007-01-01',30,10
insert into @b select '2007-01-02',40,50
insert into @b select '2007-02-03',10,10
insert into @b select '2007-03-05',10,20select * into #temp from @a
insert into #temp select * from @b
select datepart(mm,data) data,sum(a1) a1,sum(a2) a2 from #temp group by datepart(mm,data)