OracleDb中的未知資料庫函式“DATE

下面是我的BUS_DATA桌子的樣子

OracleDb 中的未知資料庫函式“DATE_FORMAT”

下面是我的查詢，它在一天中選擇最大值currentSpeed并為所選的 busId 回傳該行

選擇 busId、busName、passengersNo、speedLimit、dataDateTime、DATE_FORMAT(dataDateTime, '%Y-%m-%d') dataDate , max(currentSpeed) currentSpeed from BUS_DATA where busId = '4-3323309834' GROUP BY busId, dataDate

下面是這個查詢在運行時的樣子

OracleDb 中的未知資料庫函式“DATE_FORMAT”

這一切在我使用時運行良好，MySQL直到我遷移到Oracle RDBMS我遇到錯誤的地方


Unknown database function 'DATE_FORMAT'

Column 'busName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Column 'passengersNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Column 'speedLimit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Column 'speedLimit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

我試圖修改DATE_FORMATto_char(dataDateTime, 'YYYY-MM-DD') 并使查詢看起來像下面但它仍然顯示錯誤如何將上述查詢轉換為 Oracle RDBMS


SELECT busId, busName, passengersNo, speedLimit, dataDateTime, to_char(dataDateTime, 'YYYY-MM-DD') dataDate ,
  max(currentSpeed) currentSpeed from BUS_DATA where busId = '4-3323309834' GROUP BY busId, dataDate

dataDateTime我表中的欄位是時間戳

uj5u.com熱心網友回復：

使用GROUP BY時，要么需要在GROUP BY子句中包含列，要么需要使用聚合函式，并且不能在子句中使用在SELECT子句中宣告的別名GROUP BY：

SELECT busId,
       to_char(dataDateTime, 'YYYY-MM-DD') AS dataDate,
       max(currentSpeed) AS currentSpeed
from   BUS_DATA
where  busId = '4-3323309834'
GROUP BY
       busId,
       to_char(dataDateTime, 'YYYY-MM-DD')

如果要添加更多列，則：

SELECT busId,
       busName,
       passengersNo,
       speedLimit,
       dataDateTime,
       to_char(dataDateTime, 'YYYY-MM-DD') AS dataDate ,
       max(currentSpeed) AS currentSpeed
from   BUS_DATA
where  busId = '4-3323309834'
GROUP BY
       busId,
       busName,
       passengersNo,
       speedLimit,
       dataDateTime

或者：

SELECT busId,
       MAX(busName) AS busName,
       MAX(passengersNo) AS passengerNo,
       MAX(speedLimit) AS speedLimit,
       MAX(dataDateTime) AS dateDateTime,
       to_char(dataDateTime, 'YYYY-MM-DD') AS dataDate ,
       max(currentSpeed) AS currentSpeed
from   BUS_DATA
where  busId = '4-3323309834'
GROUP BY
       busId,
       to_char(dataDateTime, 'YYYY-MM-DD')

或不使用GROUP BY：

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY busId, to_char(dataDateTime, 'YYYY-MM-DD')
           ORDER BY currentSpeed DESC
         ) AS rn
  from   BUS_DATA t
  where  busId = '4-3323309834'
)
WHERE rn = 1;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/508620.html

標籤：mysql甲骨文约会时间通过...分组到字符

上一篇：計算表中每一列的空值

下一篇：在多個函式中訪問Python中的多個回傳值

OracleDb中的未知資料庫函式“DATE_FORMAT”