在SQL中选择连续编号

本文介绍了在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