gpt4 book ai didi

SQL 选择,填充按时间顺序丢失的月份

转载 作者:行者123 更新时间:2023-12-04 13:34:30 28 4
gpt4 key购买 nike

请注意 2015-022015-03来自 SQL 的以下组的输出中缺少月份.如果一个月没有数据,我想显示月份和 0 .有谁知道如何解决这个问题?

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

仍然无法清除丢失的行,这就是我所在的地方..
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)

最佳答案

您需要一个涵盖整个时期的日期/月份列表。这是使用递归 CTE 的一种方法:

with months as (
select cast(getdate() - 365) as thedate
union all
select date_add(1, month, thedate)
from months
where thedate <= getdate()
)
select convert(char(7), m.thedate, 121) as yyyy-mm, count(t.MeterReadDate)
from months m left join
[myTable] t
on convert(char(7), MeterReadDate, 121) = convert(char(7), m.thedate, 121)
group by convert(char(7), m.thedate, 121)
order by convert(char(7), m.thedate, 121);

关于SQL 选择,填充按时间顺序丢失的月份,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30480383/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com