我希望以月为单位选择天数，并按天数在四个时间范围内计数
我希望以月为单位选择天数，并按天数在四个时间范围内计数
提问于 2018-06-28 02:53:34
问题是范围‘三’，从晚上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
回答 1
Stack Overflow用户
发布于 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。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51069653
复制相关文章
相似问题
腾讯云开发者
