sql - T-SQL 每小时获取行数，无论日期如何

Question

使用这篇文章中已接受的答案Count rows per hour in SQL Server with full date-time value as result我能够按小时获取记录计数，但是，我想这样做，以便日期并不重要。

如何获取一天中每小时的记录计数，无论记录来自哪一天？

例如下表:

+----------+-------------------------+
| instance |        datetime         |
+----------+-------------------------+
| A        | 2017-01-10 11:01:05.267 |
| B        | 2017-01-13 11:07:05.783 |
| C        | 2017-01-14 11:37:05.593 |
| D        | 2017-01-17 11:37:38.610 |
| E        | 2017-01-17 11:47:04.877 |
| F        | 2017-01-15 12:14:34.127 |
| G        | 2017-01-17 12:15:09.373 |
| H        | 2017-01-09 13:58:06.013 |
+----------+-------------------------+

将产生以下数据集:

+-------------+-------------+
| recordCount |  timeStamp  |
+-------------+-------------+
|           5 | 11:00:00.00 |
|           2 | 12:00:00.00 |
|           1 | 13:00:00.00 |
+-------------+-------------+

Answer 1

您可以使用DATEPART :

SELECT DATEPART(HOUR, [datetime]), COUNT(*)
FROM mytable
GROUP BY DATEPART(HOUR, [datetime])

该函数返回一个整数，本质上是从 DATETIME 字段中提取小时值。