SQL Server组按DateTime每小时计数？ [英] SQL Server Group by Count of DateTime Per Hour?

问题描述

    create table #Events
(
    EventID int identity primary key,
    StartDate datetime not null,
    EndDate datetime not null
)
go
insert into #Events (StartDate, EndDate)
select '2007-01-01 12:44:12 AM', '2007-01-01 12:45:34 AM' union all
select '2007-01-01 12:45:12 AM', '2007-01-01 12:46:34 AM' union all
select '2007-01-01 12:46:12 AM', '2007-01-01 12:47:34 AM' union all
select '2007-01-02 5:01:08 AM', '2007-01-02 5:05:37 AM' union all
select '2007-01-02 5:50:08 AM', '2007-01-02 5:55:59 AM' union all
select '2007-01-03 4:34:12 AM', '2007-01-03 4:55:18 AM' union all
select '2007-01-07 3:12:23 AM', '2007-01-07 3:52:25 AM'

（抱歉， http://www.sqlteam.com/article/working-with-time-spans和持续时间s-in-sql-server ，用于收集他们的基本sql）

(with apologies to http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server for harvesting their base sql)

我正在尝试查找一小时内发生的事件计数，因此结果设置将如下所示：

I am trying to find the count of Events that occurred in an hour, so the result set would look like this:

2007-01-01      12:00     3
2007-01-02       5:00     2
2007-01-03       4:00     1
2007-01-07       3:00     1

我已经玩了dateadd和round和分组，但没有得到它。可以帮助吗？

I have been playing with dateadd and round and grouping but not getting it. Can anyone help?

谢谢。

推荐答案

假设SQL Server 2008：

How about this? Assuming SQL Server 2008:

SELECT CAST(StartDate as date) AS ForDate,
       DATEPART(hour,StartDate) AS OnHour,
       COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
       DATEPART(hour,StartDate)

2008年前：

SELECT DATEADD(day,datediff(day,0,StartDate),0)   AS ForDate,
       DATEPART(hour,StartDate) AS OnHour,
       COUNT(*) AS Totals
FROM #Events
GROUP BY CAST(StartDate as date),
       DATEPART(hour,StartDate)

这导致：

ForDate                 | OnHour | Totals
-----------------------------------------
2011-08-09 00:00:00.000     12       3

这篇关于SQL Server组按DateTime每小时计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！