select T.ZONE as tID ,sum(case when A.PSC_ID is not null then 1 else 0 end) as tTotal from tb_psr_check A
right join (
select '00' as ZONE from tb_psr_check
union select '01' from tb_psr_check
union select '02' from tb_psr_check
union select '03' from tb_psr_check
(省略)
union select '23' from tb_psr_check ) T
on substr(T.ZONE,1,2)=to_char(psc_check_time,'HH24') group by T.Zone
显示结果如下:
TID TTOTAL
00 3
01 4
02 2
03 5
04 1
05 2
(省略)
23 3
我想添加psc_check_time大于某个时间段,显示结果还是像上面一样,请问怎么改?谢谢
right join (
select '00' as ZONE from tb_psr_check
union select '01' from tb_psr_check
union select '02' from tb_psr_check
union select '03' from tb_psr_check
(省略)
union select '23' from tb_psr_check ) T
on substr(T.ZONE,1,2)=to_char(psc_check_time,'HH24') group by T.Zone
显示结果如下:
TID TTOTAL
00 3
01 4
02 2
03 5
04 1
05 2
(省略)
23 3
我想添加psc_check_time大于某个时间段,显示结果还是像上面一样,请问怎么改?谢谢
right join (
select '00' as ZONE from tb_psr_check
union select '01' from tb_psr_check
union select '02' from tb_psr_check
union select '03' from tb_psr_check
......
union select '23' from tb_psr_check ) T
on substr(T.ZONE,1,2)=to_char(psc_check_time,'HH24')
where a.psc_check_time >= &sdate
group by T.Zone
right join (
select '00' as ZONE from tb_psr_check
union select '01' from tb_psr_check
union select '02' from tb_psr_check
union select '03' from tb_psr_check
...................................
union select '23' from tb_psr_check ) T
on substr(T.ZONE,1,2)=to_char(psc_check_time,'HH24')
where a.psc_check_time >= to_date('2006-8-16 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by T.Zone
显示结果没有任何数据
where a.psc_check_time >= to_date('2006-8-16 00:00:00','yyyy-mm-dd hh24:mi:ss')
..........------------------------
..........
where a.psc_check_time >= to_date('2002-8-16 00:00:00','yyyy-mm-dd hh24:mi:ss')
..........
right join (
select '00' as ZONE from tb_psr_check
union select '01' from tb_psr_check
union select '02' from tb_psr_check
union select '03' from tb_psr_check
...................................
union select '23' from tb_psr_check ) T
on substr(T.ZONE,1,2)=to_char(psc_check_time,'HH24')
and a.psc_check_time >= to_date('2006-8-16 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by T.Zone
不好意思,上一个写错了
right join (
select '00' as ZONE from tb_psr_check
union select '01' from tb_psr_check
union select '02' from tb_psr_check
union select '03' from tb_psr_check
...................................
union select '23' from tb_psr_check ) T
on substr(T.ZONE,1,2)=to_char(psc_check_time,'HH24')
and t.zone > to_char(&date,'hh24') -- 输入时间
group by T.Zone