sql server-在sql中选择连续数字

这感觉很简单，但我在任何地方都找不到答案
我正在尝试按一天中每小时的时间运行一个查询。所以我在小时部分做了一个Group By，但并不是所有的小时都有数据，所以有一些差距。我希望每小时显示一次，不管是否有数据。

以下是一个示例查询：

SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As Hour,
       COUNT(*) AS Count
FROM Comments
WHERE UserId = ##UserId##
GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))

我的想法是加入一个已经有数字1到24的表，这样传入的数据就会放在它的位置。

我可以用CTE吗？

WITH Hours AS (
   SELECT i As Hour    --Not Sure on this
   FROM [1,2,3...24]), --Not Sure on this
CommentTimes AS (
   SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) AS Hour,
          COUNT(*) AS Count
   FROM Comments
   WHERE UserId = ##UserId##
   GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))
)
SELECT h.Hour, c.Count
FROM Hours h
JOIN CommentTimes c ON h.Hour = c.Hour

###以下是来自Stack Exchange Data Explorer 的查询示例

您可以使用递归查询来构建一个包含所需数字的表。我们24点停在这里。然后留下来加入你的评论，以确保每一个小时都有代表性。如果你愿意的话，你可以很容易地把这些变成时间。我还更改了hour作为列名的用法，因为它是一个关键字。

;with dayHours as (
    select 1 as HourValue
    union all select hourvalue + 1
    from dayHours
    where hourValue < 24
)
,
CommentTimes As (
       SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As HourValue,
              COUNT(*) AS Count
       FROM Comments
       WHERE UserId = ##UserId##
       GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate)))
SELECT h.Hour, c.Count
FROM dayHours h
left JOIN CommentTimes c ON h.HourValue = c.HourValue

您可以使用表值构造函数：

with hours as (
    SELECT hr
    FROM  (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS b(hr) 
)
etc..

您也可以使用一个永久的辅助数字表。

http://dataeducation.com/you-require-a-numbers-table/

使用递归CTE生成小时数：

with hours as (
      select 1 as hour
      union all
      select hour + 1
      from hours
      where hour < 24
     )
. . .

那么您的完整查询需要left outer join:

with hours as (
      select 1 as hour
      union all
      select hour + 1
      from hours
      where hour < 24
     )
     CommentTimes As (
      SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As Hour,
             COUNT(*) AS Count
      FROM Comments
      WHERE UserId = ##UserId##
      GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))
     )
SELECT h.Hour, c.Count
FROM Hours h LEFT OUTER JOIN
     CommentTimes c
     ON h.Hour = c.Hour;

下面的演示不使用sql server 的recursive CTE

select h.hour ,c.count
from (
    select top 24 number + 1 as hour from master..spt_values 
    where type = 'P'
) h 
left join (
    select datepart(hour, creationdate) as hour,count(1) count
    from comments
    where userid = '9131476'
    group by datepart(hour, creationdate)
) c on h.hour = c.hour
order by h.hour;

在线演示链接：连续号码查询演示-Stack Exchange Data Explorer

基本思想是正确的，但您希望执行左联接而不是标准联接。之所以选择左侧联接，是因为您希望从左侧得到答案。

关于如何创建原始小时表，您可以直接使用以下内容创建它：

SELECT 1 as hour
UNION ALL
SELECT 2 as hour
  ...
UNION ALL
SELECT 24 as hour

或者，您可以创建一个用这些值填充的永久表。（我不记得在SqlServer上是否有更好的方法，或者是否允许选择值但不允许从表中选择值。在Oracle上，您可以从内置表"dual"中选择，该表是一个包含单行的表）。

作为这个问题的一个更通用的抽象，您可以创建Brad和Gordon建议的连续数字，并使用如下递归CTE：

WITH Numbers AS (
    SELECT 1 AS Number
    UNION ALL SELECT Number + 1
    FROM Numbers
    WHERE Number < 1000
)
SELECT * FROM Numbers
OPTION (MaxRecursion 0)

需要注意的是，如果您计划使用超过100个数字，则需要在查询末尾添加OPTION (MaxRecursion 0)以防止出现错误。在语句完成之前，最大递归100已经用完

在TSQL 中填充或使用Tally表时，通常可以看到这种技术

相关内容

最新更新

热门标签：