sql查询某一天 24小时 每小时的记录总数

[ypss_milo]

数据库


需要显示的结果





查询某一天 24小时 每小时的记录总数 为0也要显示 的出来的结果肯定是24行

[快溜]

select convert(varchar(13),created,20),count(*) from tb group by convert(varchar(13),created,20)

[道玄希言]

DECLARE @dt datetime
SET @dt = '2016-01-01'

;WITH T1(id) AS
(
    SELECT 1 UNION ALL 
    SELECT 2 UNION ALL 
    SELECT 3 UNION ALL 
    SELECT 4 UNION ALL 
    SELECT 5 UNION ALL 
    SELECT 6 UNION ALL 
    SELECT 7 UNION ALL 
    SELECT 8 UNION ALL 
    SELECT 9 UNION ALL 
    SELECT 10 UNION ALL 
    SELECT 11 UNION ALL 
    SELECT 12 UNION ALL 
    SELECT 13 UNION ALL 
    SELECT 14 UNION ALL 
    SELECT 15 UNION ALL 
    SELECT 16 UNION ALL 
    SELECT 17 UNION ALL 
    SELECT 18 UNION ALL 
    SELECT 19 UNION ALL 
    SELECT 20 UNION ALL 
    SELECT 21 UNION ALL 
    SELECT 22 UNION ALL 
    SELECT 23 UNION ALL 
    SELECT 24    
),
T2 AS 
(
   SELECT DATENAME(Hour,Created) AS id, COUNT(1) AS num
   FROM 表 
   WHERE created = @dt
   GROUP BY DATENAME(Hour,Created)
)
SELECT T1.id AS [小时], T2.num AS [数量] 
FROM T1
LEFT JOIN T2
ON T1.id =T2.id
ORDER BY T1.id

[BlankRen]

我只能帮你顶起来了，这个问题，我也遇到了，至今没有结局，我去试试3楼的去

[ypss_milo]

怎么没人回答

[xiatiansiyu]

select DATEPART(HOUR,Created),COUNT(*)
	from tablename
Group by DATEPART(HOUR,Created)