使用本文中接受的答案 在 SQL Server 中每小时计算行数,结果是完整的日期时间值,我能够按小时获得记录计数,但是,我想这样做,这样一天就无关紧要了。
如何按一天中的小时获取记录计数,无论记录来自哪一天?
例如,下表:
+----------+-------------------------+
| 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 |
+-------------+-------------+
您可以使用
DATEPART
:
SELECT DATEPART(HOUR, [datetime]), COUNT(*)
FROM mytable
GROUP BY DATEPART(HOUR, [datetime])
该函数返回一个整数,实质上是从DATETIME
字段中提取小时值。
如果您
使用的是SQL SERVER 2012
或以上,请使用FORMAT
SELECT Count(1) AS Recordcount,
Format([datetime], 'HH:00:00')
FROM yourtable
GROUP BY Format([datetime], 'HH:00:00')
或CONCAT
和DATEPART
SELECT Concat(Datepart(HOUR, [datetime]), ':00:00'),
Count(*)
FROM Yourtable
GROUP BY Datepart(HOUR, [datetime])