问题是范围‘三’,从晚上10点数到第二天早上6点,结果是在前一天,我有这个问题谁给我错误的数据报告,任何解决方案。
select TRUNC (A.time)+06/24,
count (distinct B.code)as FOUR,
count(case when to_char(A.time,'HH24:MI:SS') between '06:00:00'
and '14:00:00'
then A.sn end) as ONE,
count(case when to_char(A.time,'HH24:MI:SS') between '14:00:00'
and '22:00:00'
then A.sn end) as TWO,
count(case when A.time between TO_DATE ('10:00:00 PM', 'hh:mi:ss AM')
and TO_DATE ('10:00:00 PM', 'hh:mi:ss AM')+6/24
then A.sn end) as THREE
from B
inner join A
on B.bol_id = A.bol_id
where B.group = '9'
and A.time between '01-JUN-18 06:00:00' and '25-JUN-18 06:00:00'
GROUP BY TRUNC (A.time)
我想要这样的结构example
发布于 2018-06-28 04:07:24
您的问题出在GROUP BY TRUNC (A.time)
中--这说明要在午夜开始每一行/天的窗口,并在第二天晚上11:59结束。但是你想要从当天早上6点到第二天早上5点59分。所以你想要GROUP BY TRUNC(A.time - 6/24)
-这样,今天早上5:59将被计为昨天,明天5:59将被计为今天。
然后,您可以将case THREE
修改为:
count(case when to_char(A.time,'HH24:MI:SS') > '22:00:00'
or to_char(A.time,'HH24:MI:SS') < '06:00:00'
then A.sn end) as THREE
试一试,如果你有任何问题,请告诉我们。
select TRUNC(A.time-(6/24)),
count (distinct B.code)as FOUR,
count(case when to_char(A.time,'HH24:MI:SS') between '06:00:00'
and '14:00:00'
then A.sn end) as ONE,
count(case when to_char(A.time,'HH24:MI:SS') between '14:00:00'
and '22:00:00'
then A.sn end) as TWO,
count(case when to_char(A.time,'HH24:MI:SS') > '22:00:00'
or to_char(A.time,'HH24:MI:SS') < '06:00:00'
then A.sn end) as THREE
from B
inner join A
on B.bol_id = A.bol_id
where B.group = '9'
and A.time between '01-JUN-18 06:00:00' and '25-JUN-18 06:00:00'
GROUP BY TRUNC(A.time-(6/24))
您可能还希望将大小写从between
更改为> and <=
。between X and Y
包括两个终结值,因此在下午2点发生的任何事件都将被重复计数为1和2。
https://stackoverflow.com/questions/51069653
复制相似问题