尝试将查询分组以按小时显示日事件

afdcj2ne 于 2022-09-18 发布在 Java

关注(0)|答案(1)|浏览(91)

我正在尝试从Oracle查询中获取每日计数的帐户，以按小时显示从14：00到19：00的计数。我正在使用这个查询。我想对计数输出进行分组。

Select count(*), extract(hour from eventtime) as hours
from   TR_MFS_LOADCARRIER
WHERE  eventid = 5
And    eventtime BETWEEN to_date('05/09/2022 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
                     and to_date('05/09/2022 19:00:00', 'dd/mm/yyyy hh24:mi:ss')
group by hours

它失败了，我哪里错了。

oracle

来源：https://stackoverflow.com/questions/73661559/trying-to-group-a-query-to-display-day-event-by-hours

1条答案

按热度按时间

2fjabf4q1#

hours别名是在GROUP BY子句求值后在SELECT子句中定义的，因此不能在GROUP BY子句中使用；请改用EXTRACT(hour from eventtime)。

Select count(*),
       extract(hour from eventtime) as hours
from   TR_MFS_LOADCARRIER
WHERE  eventid = 5
And    eventtime BETWEEN to_date('05/09/2022 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
                     and to_date('05/09/2022 19:00:00', 'dd/mm/yyyy hh24:mi:ss')
group by extract(hour from eventtime)

如果您的eventtime列是DATE数据类型，则不能EXTRACT小时字段，需要将其转换为TIMESTAMP数据类型：

Select count(*),
       extract(hour from CAST(eventtime AS TIMESTAMP)) as hours
from   TR_MFS_LOADCARRIER
WHERE  eventid = 5
And    eventtime BETWEEN to_date('05/09/2022 14:00:00', 'dd/mm/yyyy hh24:mi:ss')
                     and to_date('05/09/2022 19:00:00', 'dd/mm/yyyy hh24:mi:ss')
group by extract(hour from CAST(eventtime AS TIMESTAMP))

fiddle

赞(0）回复(0）举报 2022-09-18

我来回答

尝试将查询分组以按小时显示日事件

1条答案

相关问题

热门标签

最新问答