我正在嘗試從 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
它失敗了我哪里出錯了。
uj5u.com熱心網友回復：
hours別名是在對子句求值SELECT后在子句中定義的，GROUP BY因此不能在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))
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/506868.html