SQL:添加不同年份的缺失月份

SQL服务器
[创建时间] - 日期时间
我得到这张表:

Year  Month  Count
2009    7     1
2009    9     1
2010    1     2
2010    3     13

从查询:

SELECT
   YEAR ([CreatedOn]) AS 'Year',
   MONTH ([CreatedOn]) AS 'Month',
   COUNT ([CreatedOn]) AS 'Count'
FROM xxx
GROUP BY YEAR ([CreatedOn]), MONTH ([CreatedOn])

我怎样才能得到这样的表(错过了几个月和 Count 0):

Year  Month  Count
2009    7     1
2009    8     0
2009    9     1
2009    10    0
2009    11    0
2009    12    0
2010    1     2
2010    2     0
2010    3     13

最佳答案

语法说您正在使用 MSSQL 。使用 Recursive CTE 生成 calender 表然后用 Left outer join 表做一个 XXX

DECLARE @maxdate DATE = (SELECT Max([CreatedOn])
   FROM   xxx);

WITH calender
     AS (SELECT Min([CreatedOn]) dates,
         FROM   xxx
         UNION ALL
         SELECT Dateadd(mm, 1, dates)
         FROM   cte
         WHERE  dates < @maxdate)
SELECT Year(dates)         [YEAR],
       Month(dates)        [month],
       Count ([CreatedOn]) AS 'Count'
FROM   calender a
       LEFT OUTER JOIN xxx b
                    ON Year(dates) = Year ([CreatedOn])
                       AND Month(dates) = Month ([CreatedOn])
GROUP  BY Year(dates),
          Month(dates)

注意: 代替 Recursive CTE 创建一个物理日历表

关于SQL:添加不同年份的缺失月份，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28299719/