如果您不希望超出结果的日期
min和
max日期，则可以执行以下操作：
WITH cte AS ( SELECt convert(char(7), MeterReadDate, 121) AS [Date], COUNT(*) AS [Count] FROM [myTable] WHERe (MeterReadDate > dateadd(d,-356,getdate())) GROUP by convert(char(7), MeterReadDate, 121) ), minmax AS ( SELECt CAST(MIN([Date] + '-01') AS DATE) AS mind , CAST(MAX([Date] + '-01') AS DATE) maxd FROM cte ), calendar AS ( SELECt mind , ConVERT(CHAr(7), mind, 121) AS cmind FROM minmax UNIOn ALL SELECt DATEADD(mm, 1, calendar.mind) , ConVERT(CHAr(7), DATEADD(mm, 1, calendar.mind), 121) FROM calendar CROSS JOIN minmax WHERe calendar.mind < minmax.maxd ) SELECt c.cmind AS [Date], ISNULL(cte.[Count], 0) AS [Count] FROM calendar c LEFT JOIN cte ON c.cmind = cte.[Date] OPTION ( MAXRECURSION 0 )
