请注意,sql在以下组的输出中缺少2015-02和2015-03个月.如果一个月没有数据,我想显示月份和0.任何人都知道如何去做这个？
SELECT convert(char(7),MeterReadDate,121),count(*) FROM [myTable] where (MeterReadDate > dateadd(d,-356,getdate())) group by convert(char(7),121) order by convert(char(7),121)
样本数据：
YYYY-MM COUNT 2014-06 23 2014-07 42 2014-08 80 2014-09 92 2014-10 232 2014-11 88 2014-12 8 2015-01 5 2015-04 2 2015-05 1
仍然无法清除缺失的行,这里是我的地方..
DECLARE @StartDate DATETIME = dateadd(m,-12,getdate()),@EndDate DATETIME = getdate(),@DATE DATETIME
DECLARE @TEMP AS TABLE (MeterReadDate datetime)
SET @DATE = @StartDate
WHILE @DATE <= @EndDate
BEGIN
INSERT INTO @TEMP VALUES ( @DATE)
SET @DATE = DATEADD(MONTH,1,@DATE)
END
SELECT convert(char(7),t.MeterReadDate,count(*)
FROM @TEMP m left join
[myTable] t
on convert(char(7),121) = convert(char(7),m.MeterReadDate,121)
where (t.MeterReadDate > dateadd(m,getdate()))
group by convert(char(7),121)
order by convert(char(7),121)
解决方法
如果您不想超出结果的最短和最长日期,则可以执行以下操作：
WITH cte
AS ( SELECT convert(char(7),121) AS [Date],COUNT(*) AS [Count]
FROM [myTable]
WHERE (MeterReadDate > dateadd(d,getdate()))
GROUP by convert(char(7),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,calendar.mind),DATEADD(mm,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 )
