查询24小时各时间段的记录

Question

表tb_psr_check结构如下：
     ID（主键） CHANNEL  Time
数据：1         D001     2006-8-7 16:27:29
      2         D002     2006-8-7 17:10:49
      3         D001     2006-8-7 18:10:49
      4         D001     2006-8-7 18:20:49
统计各时间段的CHANNEL的个数
16-17：     1
17-18：     1
18-19：     2

Answer 1

select to_char(time, 'hh24'), count(*) from tb_psr_check
    group by to_char(time, 'hh24');

Answer 2

select to_char("Time",'yyyy-mm-dd HH24')||'-'||to_char(to_number(to_char("Time",'HH24'))+1) 
       ,count(1)
from 表
group by to_char("Time",'yyyy-mm-dd HH24')
         ,to_char("Time",'HH24')

Answer 3

如果只按时段统计：select T.ZONE 
       ,sum(case when A.ID is not null then 1 else 0 end)
from A 
right join 
     ( 
       select '00-01' as "ZONE" from dual
       union
       select '01-02' from dual
       union
       select '02-03' from dual
       union
       select '03-04' from dual
       union
       select '04-05' from dual
       union
       select '05-06' from dual
       union
       select '06-07' from dual
       union
       select '07-08' from dual
       union
       select '08-09' from dual
       union
       select '09-10' from dual
       union
       select '11-12' from dual
       union
       select '12-13' from dual
       union
       select '13-14' from dual
       union
       select '14-15' from dual
       union
       select '15-16' from dual
       union
       select '16-17' from dual
       union
       select '17-18' from dual
       union
       select '18-19' from dual
       union
       select '19-20' from dual
       union
       select '21-22' from dual
       union
       select '22-23' from dual
       union
       select '23-24' from dual
     ) T on substr(T.ZONE,1,2)=to_char("Time",'HH24')
group by T.Zone
         
         

Answer 4

SQL> select to_char(Time,'hh24')||'-'||(to_char(Time,'hh24')+1) rq,count(0) tj from tb_psr_check group by to_char(Time,'hh24');RQ                                                  TJ
------------------------------------------- ----------
16-17                                                1
17-18                                                1
18-19                                                2Executed in 0.016 secondsSQL>