SQL Server 2008按小时统计表中记录数的实现方案

首先，用DATEPART(HOUR, [Time])来精准提取时间字段的小时部分，比用LIKE匹配字符串更可靠（能避免时间格式不一致的问题，比如单数字小时的情况）。然后通过条件聚合的方式，为每个小时生成对应的统计列，确保每个小时（0-23）都有结果，无数据时显示0。

完整SQL语句 #

SELECT
    Column0 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 0 THEN 1 ELSE 0 END),
    Column1 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 1 THEN 1 ELSE 0 END),
    Column2 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 2 THEN 1 ELSE 0 END),
    Column3 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 3 THEN 1 ELSE 0 END),
    Column4 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 4 THEN 1 ELSE 0 END),
    Column5 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 5 THEN 1 ELSE 0 END),
    Column6 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 6 THEN 1 ELSE 0 END),
    Column7 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 7 THEN 1 ELSE 0 END),
    Column8 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 8 THEN 1 ELSE 0 END),
    Column9 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 9 THEN 1 ELSE 0 END),
    Column10 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 10 THEN 1 ELSE 0 END),
    Column11 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 11 THEN 1 ELSE 0 END),
    Column12 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 12 THEN 1 ELSE 0 END),
    Column13 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 13 THEN 1 ELSE 0 END),
    Column14 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 14 THEN 1 ELSE 0 END),
    Column15 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 15 THEN 1 ELSE 0 END),
    Column16 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 16 THEN 1 ELSE 0 END),
    Column17 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 17 THEN 1 ELSE 0 END),
    Column18 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 18 THEN 1 ELSE 0 END),
    Column19 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 19 THEN 1 ELSE 0 END),
    Column20 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 20 THEN 1 ELSE 0 END),
    Column21 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 21 THEN 1 ELSE 0 END),
    Column22 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 22 THEN 1 ELSE 0 END),
    Column23 = SUM(CASE WHEN DATEPART(HOUR, [Time]) = 23 THEN 1 ELSE 0 END)
FROM
    YourTableName; -- 替换成你的实际表名

关键说明 #

• 使用DATEPART(HOUR, [Time])提取小时：这个函数直接从time类型字段中获取小时数值（0-23），比字符串匹配更精准，不会因为时间格式（如8:30 vs 08:30）出现错误。
• 条件聚合SUM(CASE ...)：对于每一行数据，判断其小时是否匹配当前列对应的小时，匹配则计数1，否则0，最后求和得到该小时的总Case数。即使某个小时没有数据，SUM会返回0，满足需求中“覆盖所有小时”的要求。
• 注意Time是SQL Server的保留关键字，所以查询中要用方括号[Time]包裹，避免语法错误。

测试结果（基于你的示例数据） #

对于你给出的示例数据：

执行上述SQL后，结果会是：

内容的提问来源于stack exchange，提问作者Eray Balkanli