结合两个按月计数的查询

Question

受此问题的启发，我在Stack Exchange DataExplorer上编写了两个查询，一个查询统计了SO上每月提出的问题总数，另一个查询了Month颁发的赏金。如何合并它们，以便在一个查询中获得输出？我想在一份报告中查看年，月，问题，赏金和金额。

问题记录在PostTypeId = 1的Posts表中，但赏金记录在VoteTypeId = 9的Votes表中。

Answer 1

我是在记事本中写的，还没有在SO上与数据资源管理器一起工作。

select Isnull(V.Year, P.Year) As Year,
Isnull(V.Month, P.Month) As Month,
Isnull(V.Bounties, 0) As Bounties,
Isnull(V.Amount,0) As Amount ,
P.Questions
FROM
(
select
datepart(year, Posts.CreationDate) Year,
datepart(month, Posts.CreationDate) Month,
count(Posts.Id) Questions
from Posts
where PostTypeid = 1 -- 1 = Question
group by datepart(year, Posts.CreationDate), datepart(month, Posts.CreationDate)
) AS P
left JOIN
(
select
datepart(year, Votes.CreationDate) Year,
datepart(month, Votes.CreationDate) Month,
count(Votes.Id) Bounties,
sum(Votes.BountyAmount) Amount
from Votes
where VoteTypeId = 9 -- 9 = BountyAwarded
group by datepart(year, Votes.CreationDate), datepart(month, Votes.CreationDate)
) AS V
ON P.Year = V.Year AND P.Month = V.Month
order by P.Year, P.Month