SQL选择，按时间顺序缺少月份

SQL选择，按时间顺序缺少月份

如果您不希望超出结果的日期

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 )