SQL - 显示两个日期之间的所有日期

Question

SQL - 显示两个日期之间的所有日期

3

我在[OccuredAtUtc]中保存的数据是日期，格式如下：

-- 剧透提示：“2017-04-26”和“2017-04-29”缺失。

[OccuredAtUtc]中的原始日期：

2017-04-24 12:16:58.5080000
2017-04-24 18:11:53.3090000
2017-04-25 18:34:18.3090000
2017-04-27 20:42:28.8570000
2017-04-28 21:10:36.7070000
2016-04-28 10:37:57.5970000
2016-04-30 10:38:55.7010000
2016-04-30 10:48:19.0390000
2016-04-31 10:48:19.2990000
.
.
.

我有这段代码，可以正确返回两个时间间隔（上周）的数据。

SELECT 
        [MessageType].[Name] AS [Channel],
        CONVERT(VARCHAR(11), [OccuredAtUtc], 106) AS [Time],
        COUNT(*) AS [Count]
FROM @table1
        INNER JOIN @table2 ON ... = ...
WHERE ( [OccuredAtUtc] > '2017-04-24'
        AND [OccuredAtUtc] < '2017-04-30' )
GROUP BY (CONVERT(VARCHAR(11), [OccuredAtUtc], 106)),
         [MessageType].[Name]
ORDER BY [Time] ASC

但是输出结果不会显示“2017年4月26日”和“2017年4月29日”的数据行，因为在我的数据库中这些日期没有记录。 旧的输出结果：缺少4月26日和4月29日的数据。

[Channel]       [Time]          [Count]
------------------------------------
FTP           24 Apr 2017         7
HTTP          24 Apr 2017         9
FTP           25 Apr 2017         6
HTTP          25 Apr 2017         2
------MISSING 26 Apr--------
FTP           27 Apr 2017         56
HTTP          27 Apr 2017         12
FTP           28 Apr 2017         5
------MISSING 29 Apr--------
HTTP          28 Apr 2017         17
FTP           30 Apr 2017         156
HTTP          30 Apr 2017         19

我希望能够显示缺失日期的行，即使在这一天没有事件保存...

所以新的输出应该是这样的。

想要的输出:

[Channel]       [Time]          [Count]
------------------------------------
FTP             24 Apr 2017        7
HTTP            24 Apr 2017        9
FTP             25 Apr 2017        6
HTTP            25 Apr 2017        2
0               26 Apr 2017        0  -- here we go
FTP             27 Apr 2017        56
HTTP            27 Apr 2017        12
FTP             28 Apr 2017        5
HTTP            28 Apr 2017        17
0               29 Apr 2017        0  -- here we go
FTP             30 Apr 2017        156
HTTP            30 Apr 2017        19

我知道有类似于我的问题已经得到了回答，我试图重写我的代码但是失败了。

（显示两个日期之间的所有日期数据；如果特定日期没有行，则在所有列中显示零）

（如何生成两个日期之间的所有日期）

- Radim Šafrán

请检查我的答案 - DhruvJoshi

3个回答

0

你可以使用类似于计数表的东西来生成在某个时间间隔内的所有日期。

SELECT 
        ISNULL([MessageType].[Name],0) AS [Channel],
        dates AS [Time],
        COUNT([MessageType].[Name]) AS [Count]
FROM 
(
   SELECT 
      TOP (DATEDIFF(d,'2017-04-24','2017-04-30')+1) 
       DATEADD(d,ROW_NUMBER() OVER( ORDER BY (SELECT 1))-1,'2017-04-24') dates
    FROM sys.objects a CROSS JOIN sys.objects b 
)T
LEFT JOIN 
@table1 ON T.dates=CONVERT(VARCHAR(11), @table1.[OccuredAtUtc], 106)
        LEFT JOIN @table2 ON ... = ...
AND ( [OccuredAtUtc] > '2017-04-24'
        AND [OccuredAtUtc] < '2017-04-30' )
GROUP BY dates,
         [MessageType].[Name]
ORDER BY [Time] ASC

关于计数表的更多说明，请阅读本文

- DhruvJoshi

我不认识Joshi...首先感谢您提供有关Tally表的提示，我从未听说过这样的东西。无论如何，我尝试了这段代码，它仍然没有显示缺失的日期，而列中的输出看起来像“2017-04-18 00:00:00.000”，而不是“2017年4月24日”。 - Radim Šafrán

难道没有一种“优雅的解决方案”来解决我的问题吗？我所需要的只是显示间隔中缺失的日期... :/ - Radim Šafrán

1

对于“列中的输出看起来像是'2017-04-18 00:00:00.000'而不是'2017年4月24日'”，您可以随时正确地进行格式化。 - DhruvJoshi

对于“仍然没有显示缺失的日期”，那是由于WHERE条件引起的。我已经更正了我的答案，请现在尝试。 - DhruvJoshi

仍然没有显示缺失的日期 :( 您可以在此处查看：https://ibb.co/chHUt5 这是一个区间的示例，即16 Apr - 23 Apr（两周前），其中17 Apr没有记录...因此，输出中也不会显示17 Apr。--对于一周前，它的行为方式相同。你认为怎么样，Joshi？附注：这不是DB的问题。 - Radim Šafrán

@RadimŠafrán 好的，我看到问题所在了。是INNER JOIN的问题。我更新了我的答案。 - DhruvJoshi

0

declare @t table ( i int identity , b bit, d as dateadd (dd, i - 1, 0 ))

insert into @t (b)
VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0)

insert into @t (b)
select 0
from @t t1
cross apply ( select b from @t) as t2
cross apply ( select b from @t) as t3
cross apply ( select b from @t) as t4
cross apply ( select b from @t) as t5

select t.d, isnull(y.channel,0), count(y.[date])
from @t t
    left join yourtable y on y.[date] = t.d
where d between getdate() - 30 and getdate()
group by t.d, isnull(y.channel,0)

- Mikhail Sokolov

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alex · Accepted Answer

类似于@DhruvJoshi的答案，但使用递归CTE生成日期：

DECLARE @MinDate DATE = '20170424',
        @MaxDate DATE = '20170430';

WITH allDates AS
(
    SELECT @MinDate AS dates

    UNION ALL

    SELECT DATEADD(DAY, 1, ad.[dates] )
    FROM allDates AS ad
    WHERE ad.[dates] < @MaxDate
)

SELECT 
        ISNULL([MessageType].[Name],0) AS [Channel],
        dates AS [Time],
        COUNT([MessageType].[Name]) AS [Count]
FROM 
(
   SELECT dates
   FROM allDates
) AS T
LEFT JOIN 
@table1 ON T.dates=CONVERT(VARCHAR(11), @table1.[OccuredAtUtc], 106)
        LEFT JOIN @table2 ON ... = ...
GROUP BY dates,
         [MessageType].[Name]
ORDER BY [Time] ASC