按每小时日期时间计数的 SQLServer 组? ？

    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'

(为收获他们的基础 sql 向 http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server道歉)

我试图找到一个小时内发生的事件的计数，因此结果集应该是这样的:

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

我一直在玩日期添加，轮流和分组，但没有得到它。有人能帮忙吗？

谢谢。

156557 次浏览
小开
最佳答案

假设 SQLServer2008:

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
小开

或者，只需要每小时每天的 GROUP BY:

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

产出:

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
小开

我在别处找到的，我喜欢这个答案！

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

您还可以通过使用以下 SQL 来实现这一点: 在相同的列中使用日期和小时，采用适当的日期时间格式，并按日期时间排序

SELECT  dateadd(hour, datediff(hour, 0, StartDate), 0) as 'ForDate',
COUNT(*) as 'Count'
FROM #Events
GROUP BY dateadd(hour, datediff(hour, 0, LogTime), 0)
ORDER BY ForDate

提交答案