多表查询加月统计问题

[wwdyx]

请教一下:
两个表: A ,B
A表如下
data a1 a2
2007-01-01 20 30
2007-01-02 10 40
2007-02-03 30 10
2007-08-05 10 20
B表如下
data b1 b2
2007-01-02 30 10
2007-01-05 40 50
2007-02-03 10 10
2007-03-05 10 20

希望得到按月统计两表的a1+b1的和a2+b2的和
结果如下:
month a1+b1 a2+b2
1 100 130
2 40 20
3 10 20
8 10 20

[boblaw]

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作为条件

[wwdyx]

非常感谢楼上两位．

[wwdyx]

两位朋友的都可以经测试都可以达到要的效果，就是时间有点慢，都要40秒再能出现结果．不过还是非常感谢两位．呵

[-狙击手-]

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)

[pt1314917]

用临时表。效率高
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,20

declare @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,20

select * 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)