SQL如何对两个日期之间的一小时内的事件求和，并将其显示在一行中 [英] SQL how to sum events in one hour between two dates and show it in one row

问题描述

我正在用C＃和SQL server 2005开发一个报表，
我只能显示每小时有多少次命中。表是非常巨大的。
输出应该如下所示：

I am developing a report with C# and SQL server 2005, I have to only show how many hit we got in each hour. the table is very huge. output should look like this:

Row# |     Date    |  Time  | Hit Count
-----------------------------
1    | 07/05/2012  |  8:00  |  3
2    | 07/05/2012  |  9:00  |  4
3    | 07/05/2012  |  10:00 |  0
4    | 07/05/2012  |  11:00 |  5

我的表格如下所示：

"HitTime":

07/05/2012 08:02:24
07/05/2012 08:12:21
07/05/2012 08:23:00
07/05/2012 09:01:00
07/05/2012 09:08:14
07/05/2012 09:12:31
07/05/2012 09:22:27

.. etc
正如你在HitTime字段中看到的，我只有日期和时间，我需要在同一天显示，从例如8:00到8:59我获得了多少命中，它应该是一整天，从第一天开始，直到非常结束第二天。 p>

..etc As you see in HitTime field I only have date and time, I need to show at same date, from for example 8:00 till 8:59 how many hit did I get, and it should be for all day, from very first second that day starts till very end second of the day.

推荐答案

DECLARE @current_date DATETIME

SET @current_date = '2012-05-07';

WITH    hours (hr) AS
        (
        SELECT  0
        UNION ALL
        SELECT  hr + 1
        FROM    hours
        WHERE   hr < 23
        )
SELECT  ROW_NUMBER() OVER (ORDER BY hr) AS rn,
        @current_date AS [date],
        CONVERT(VARCHAR(5), DATEADD(hour, h.hr, @current_date), 108) AS [time],
        COUNT(hs.hittime) AS hitcount
FROM    hours h
LEFT JOIN
        hits hs
ON      hs.hittime >= DATEADD(hour, h.hr, @current_date)
        AND hs.hittime < DATEADD(hour, h.hr + 1, @current_date)
GROUP BY
        hr

这篇关于SQL如何对两个日期之间的一小时内的事件求和，并将其显示在一行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！