将我的结果合并到一个平面表格中

Question

sql语句

select a.Date, count(a.Id) as Apples from dbo.apples as a group by a.Date

假设我有一个产生结果的

Date      Apples
10/1/2011 4
10/2/2011 6
10/4/2011 8

，并且我有第二个查询

select o.Date, count(o.Id) as Oranges from dbo.Oranges as o group by o.Date

，它产生结果

Date      Oranges
10/2/2011 3
10/3/2011 5
10/4/2011 7

我想将两个结果合并成一个结果，就像

Date      Apples Oranges
10/1/2011 4      0
10/2/2011 6      3
10/3/2011 0      5
10/4/2011 8      7

我尝试过的

select select a.Date, count(a.Id) as Apples from dbo.apples as a group by a.Date
union 
select o.Date, count(o.Id) as Oranges from dbo.Oranges as o group by o.Date

那样，这没有给我预期的结果，我得到的是更多 一样

Date      Apples
10/1/2011 4   
10/2/2011 6
10/2/2011 3
10/3/2011 5
10/4/2011 8
10/4/2011 7

就像没有提到橙子

。最终的sql语句应该是什么样的（我更喜欢高性能和简洁而不是不）。

Answer 1

select date,sum(apples) as apples,sum(oranges) as Oranges
from
(
select a.Date as theDate, count(a.Id) as Apples,0 as Oranges 
from dbo.apples as a group by a.Date 
union all
select o.Date, 0 as apples, count(o.Id) as Oranges
 from dbo.Oranges as o 
 group by o.Date 
) xx
group by theDate