我正在开发用C＃和SQL Server 2005的报告，我也只显示我们每个小时有多少的命中了。 该表是非常巨大的。 输出应该是这样的：
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
..等正如你在HitTime现场看我只有日期和时间，我需要在同一日期显示，例如从8:00至8:59我怎么打多得，它应该是所有天，从第一个第二的那一天开始，直到快结束一天的第二位。
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
这个怎么样？
;WITH aggregation(hit_date, hit_time)
AS
(
SELECT DATEADD(dd, DATEDIFF(dd, 0, hittime), 0)
, DATEPART(hour, hittime)
FROM test
)
SELECT ROW_NUMBER() OVER (ORDER BY hit_date, hit_time) AS rn
, CONVERT(VARCHAR,hit_date,101) as [date]
, CAST(hit_time AS VARCHAR) + ':00' as [time]
, COUNT(*) as hit_count
FROM aggregation
GROUP BY hit_date
, hit_time
WITH hit_count AS(
select CONVERT(VARCHAR,hit_time,101)as [date], substring(convert(varchar,hit_time,108), 0, 4)+'00' as [time] from hit
)
select date,[time], count(*) as hit from hit_count group by [time],[date]
如果你想AM / PM则：
WITH hit_count AS(
select CONVERT(VARCHAR,hit_time,101)as [date], (substring(convert(varchar,hit_time,100), 12, 4)+'00'+substring(convert(varchar,hit_time,100),18,2)) as [time] from hit
)
select date,[time], count(*) as hit from hit_count group by [time],[date]
GO
