以完整的日期时间值作为结果计算 SQL Server 中每小时的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20719901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count rows per hour in SQL Server with full date-time value as result
提问by John Doe
How can I count the number of rows per hour in SQL Server with full date-time as result.
如何计算 SQL Server 中每小时的行数，结果是完整的日期时间。
I've already tried this, but it returns only the hours
我已经试过了，但它只返回小时
SELECT DATEPART(HOUR,TimeStamp), Count(*)
FROM [TEST].[dbo].[data]
GROUP BY DATEPART(HOUR,TimeStamp)
ORDER BY DATEPART(HOUR,TimeStamp)
Now the result is:
现在的结果是：
Hour Occurrence
---- ----------
10 2157
11 60740
12 66189
13 77096
14 90039
But I need this:
但我需要这个：
Timestamp Occurrence
------------------- ----------
2013-12-21 10:00:00 2157
2013-12-21 11:00:00 60740
2013-12-21 12:00:00 66189
2013-12-21 13:00:00 77096
2013-12-21 14:00:00 90039
2013-12-22 09:00:00 84838
2013-12-22 10:00:00 64238
回答by Gordon Linoff
You actually need to round the TimeStampto the hour. In SQL Server, this is a bit ugly, but easy to do:
你实际上需要四舍五入TimeStamp到小时。在 SQL Server 中，这有点难看，但很容易做到：
SELECT dateadd(hour, datediff(hour, 0, TimeStamp), 0) as TimeStampHour, Count(*)
FROM [TEST].[dbo].[data]
GROUP BY dateadd(hour, datediff(hour, 0, TimeStamp), 0)
ORDER BY dateadd(hour, datediff(hour, 0, TimeStamp), 0);
