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

小编典典

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

sql

我对sql不好，所以任何帮助世界都很棒

我有一个SQL查询，该查询获取从1月到本月注册的记录

我的代码示例

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月= 2、3月= 3、4月= 4和5月= 5

我想让它带回Jan = 1（总数为0）和June = 6（总数为0）以及任何想法如何做到这一点？

谢谢你。

阅读 221

2021-04-14

共1个答案

小编典典

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

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

2021-04-14