SQL Server Group是否按每小时的DateTime计数进行分组?

    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'

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

(请向http://www.sqlteam.com/article/working with-time- spansen - long - term -in-sql-server道歉，以获取它们的基本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

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

我一直在玩dateadd, round和分组，但没有得到它。谁能帮忙吗?

Thanks.

谢谢。

3 个解决方案

#1

How about this? Assuming SQL Server 2008:

这个怎么样?如果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)

For pre-2008:

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)

This results in :

这将导致:

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

#2

Alternatively, just GROUP BY the hour and day:

或者，只需要按小时和天数分组:

SELECT  CAST(Startdate as DATE) as 'StartDate', 
        CAST(DATEPART(Hour, StartDate) as varchar) + ':00' as 'Hour', 
        COUNT(*) as 'Ct'
FROM #Events
GROUP BY CAST(Startdate as DATE), DATEPART(Hour, StartDate)
ORDER BY CAST(Startdate as DATE) ASC

output:

输出:

StartDate   Hour    Ct
2007-01-01  0:00    3
2007-01-02  5:00    2
2007-01-03  4:00    1
2007-01-07  3:00    1

#3

I found this somewhere else. I like this answer!

我在别的地方找到的。我喜欢这个答案!

SELECT [Hourly], COUNT(*) as [Count]
  FROM 
 (SELECT dateadd(hh, datediff(hh, '20010101', [date_created]), '20010101') as [Hourly]
    FROM table) idat
 GROUP BY [Hourly]

#1