SQL 选择,填充按时间顺序丢失的月份 [英] SQL select, pad with chronological missing months
本文介绍了SQL 选择,填充按时间顺序丢失的月份的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
注意 SQL
的以下组的输出中缺少 2015-02
和 2015-03
月份.如果一个月没有数据,我想显示月份和 0
.有谁知道如何解决这个问题?
Notice the 2015-02
and 2015-03
months are missing in the output from the following group by SQL
. If there is no data for a month I want to show the month and 0
. Anyone know how to go about this?
SELECT convert(char(7), MeterReadDate, 121),count(*)
FROM [myTable]
where (MeterReadDate > dateadd(d,-356,getdate()))
group by convert(char(7), MeterReadDate, 121)
order by convert(char(7), MeterReadDate, 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
仍然无法清除丢失的行,这是我的地方..
Still cannot clear the missing rows, here is where I am with it..
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, 121),count(*)
FROM @TEMP m left join
[myTable] t
on convert(char(7), t.MeterReadDate, 121) = convert(char(7), m.MeterReadDate, 121)
where (t.MeterReadDate > dateadd(m,-12,getdate()))
group by convert(char(7), t.MeterReadDate, 121)
order by convert(char(7), t.MeterReadDate, 121)
推荐答案
如果您不想超出结果的 min
和 max
日期,那么您可以执行以下操作:
If you don't want to go beyond your min
and max
dates of your results then you can do the following:
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 )
这篇关于SQL 选择,填充按时间顺序丢失的月份的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文