在SQL中选择连续编号 [英] Select Consecutive Numbers in SQL

问题描述

这听起来很简单，但我找不到任何答案。

我想按小时在一天中的某个时间运行查询。因此，我在小时部分执行了 Group By ，但并非所有小时都有数据，因此存在一些差距。我想每小时显示一次，无论是否有数据。

This feels simple, but I can't find an answer anywhere.
I'm trying to run a query by time of day for each hour. So I'm doing a Group By on the hour part, but not all hours have data, so there are some gaps. I'd like to display every hour, regardless of whether or not there's data.

以下是示例查询：

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的表，以便将传入的数据放到该位置。

My thought was to Join to a table that already had numbers 1 through 24 so that the incoming data would get put in it's place.

我可以使用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数据资源管理器中查询

Here's a sample Query From Stack Exchange Data Explorer

推荐答案

您可以使用递归查询来构建所需数字的表。我们在这里停止在24点。然后将其与您的评论联系起来，以确保每个小时都有代表。如果需要，您可以轻松地将它们转变为时代。我还更改了您使用 hour 作为列名的原因，因为它是关键字。

You can use a recursive query to build up a table of whatever numbers you want. Here we stop at 24. Then left join that to your comments to ensure every hour is represented. You can turn these into times easily if you wanted. I also changed your use of hour as a column name as it is a keyword.

;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

这篇关于在SQL中选择连续编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！