sql查询获取从一月到当月的所有数据，即使没有记录

我不擅长 sql 所以任何帮助世界都很棒

我有一个 SQL 查询，可以获取从一月到当月注册的记录

我的代码示例

SELECT DatePart(YEAR, p.createStamp) as TheYear, DatePart(MONTH, p.createStamp) as TheMonth ,  COUNT(p.pId) AS TOTALCOUNT 
FROM profile p with(nolock)
where DatePart(YEAR, p.createStamp) = DATEPART(YEAR, GETDATE())
GROUP BY YEAR(p.createStamp), MONTH(p.createStamp)
ORDER BY YEAR(p.createStamp), MONTH(p.createStamp)

查询将如何返回

二月 = 2、三月 = 3、四月 = 4 和五月 = 5

我想让它带回 Jan = 1，总计数为 0，June = 6，总计数为 0，还有什么想法如何做到这一点？

谢谢。

这是一个创建月/年组合并将其用作查询基础的循环：

declare @startDate as datetime
set @startDate = '1/1/13'

declare @currentDate as datetime
set @currentDate = '6/6/13'

select
     month(@currentDate) as monthOfDate
    ,year(@currentDate) as yearOfDate
into #allDates
where 1=0

while (@startDate <= @currentDate)
begin
    insert into #allDates values (month(@startDate),year(@startDate))
    set @startDate = dateadd(m,1,@startDate)
end

select 
     _monthYear.yearofDate
    ,_monthYear.monthOfDate
    , COUNT(p.pId) as total
from #allDates _monthYear
left join profile p with(nolock)
    on month(p.createStamp) = _monthYear.monthOfDate
    and year(p.createStamp) = _monthYear.yearOfDate
group by
     _monthYear.yearofDate
    ,_monthYear.montOfDate

drop table #allDates