您的语法看起来像postgres，它具有 generate_series() :

select gs.hh, ultd.status, count(ultd.status) as count
from generate_series(current_date, now(), interval '1 hour') gs(hh) left join
     zainksa_mobileapp.user_logging_table_detail ultd
     on ultd.creationtime >= gs.hh and
        ultd.creationtime < gs.hh + interval '1 hour' and
        ultd.status = 'SUCCESS' 
group by gs.hh, ultd.status
order by hour asc

gordon linoff的解决方案很优雅，但我认为我应该提供一个通用的替代方案，它不依赖于特定的postgres功能：

WITH t AS
(
    SELECT m * 10 + n h
    FROM (VALUES (0), (1), (2)) v1(m)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
    where m * 10 + n < 25
)
select t.h, status as status,
    count(*) as count,
    EXTRACT(
        hour
        from creationtime
    ) AS hour,
    creationtime::date as datee
from t
 left join user_logging_table_detail
  on EXTRACT(hour from creationtime ) = t.h
   and creationtime::date = current_date
   and status = 'SUCCESS'
group by hour,
    creationtime::date,
    status
order by t.h;