asp.net - 在 SQL Server 中显示 24 小时的基于小时的数据

Question

我想显示过去 24 小时的每小时报告。我试过了，但问题是它只会在特定时间包含数据的地方显示计数。

但我想显示一个小时的计数，如果找不到计数，则在那边显示 0。

select 
    datepart(hour, upload_date) as [hour], count(*)
from 
    tbl_stories
where 
    upload_date > getdate() - 1
group by 
    datepart(hour, upload_date)

输出:

hour    count
-------------
11      2
16      1
17      1

但是我想通过下面的方式获取记录。

hour   count
-------------
1       0
2       0
3       5
.
.
.
.
24       1

Answer 1

您可以使用 value() 子句生成所有小时，然后使用 left join:

select v.hh, count(s.upload_date)
from (values (0), (1), . . . (23)
     ) v(hh) left join
     tbl_stories s
     on datepart(hour, s.upload_date) = v.hh and
        s.upload_date > getdate() - 1
group by v.hh
order by v.hh;

请注意，小时从 0 到 23。

如果您不想列出小时数，一种方便的生成方法是递归 CTE:

with hours as (
      select 1 as hh
      union all
      select hh + 1
      from hours
      where hh < 23
     )
select h.hh, count(s.upload_date)
from hours h
     tbl_stories s
     on datepart(hour, s.upload_date) = h.hh and
        s.upload_date > getdate() - 1
group by h.hh
order by h.hh;