MySQL查询迁移至Oracle遇'X'符号解析错误及结果异常求助

原MySQL查询（运行正常） #

SELECT
    X.*
FROM
    (
        SELECT
            BusID,
            BusName,
            CurrentSpeed,
            PassengersNo,
            SpeedLimit,
            dataDateTime,
            ROW_NUMBER() OVER (
                PARTITION BY CAST(dataDateTime AS DATE), BusId
                ORDER BY CurrentSpeed DESC
            ) AS RowNumOrder
    FROM
        BUS_DATA 
    WHERE
        busId = '4-3323309834'
    ) AS X 
WHERE
    X.RowNumOrder = 1

Oracle迁移后的错误提示 #

Unable to resolve symbol 'X'.

尝试的修改及问题 #

• 第一次修改：查询运行但无结果返回

SELECT *
FROM
(
SELECT
BusID
, BusName
, CurrentSpeed
, PassengersNo
, SpeedLimit
, dataDateTime
, ROW_NUMBER() OVER (PARTITION BY CAST(dataDateTime AS DATE), BusId ORDER BY CurrentSpeed DESC) AS RowNumOrder
FROM BUS_DATA 
WHERE busId = '4-3323309834'
) WHERE RowNumOrder = 1

• 第二次修改（参考user5683823建议）：系统提示需分两次运行

SELECT X.*
FROM
(
SELECT
BusID
, BusName
, CurrentSpeed
     ,PassengersNo
, SpeedLimit
, dataDateTime
, ROW_NUMBER() OVER (PARTITION BY CAST(dataDateTime AS DATE), BusId ORDER BY CurrentSpeed DESC) RowNumOrder
FROM BUS_DATA
WHERE busId = '4-3323309834'
) X
WHERE X.RowNumOrder = 1

• 第三次修改（参考Rick James建议）：报错「Unable to resolve column 'RowNumOrder'」

SELECT
BusID
, BusName
, CurrentSpeed
     ,PassengersNo
, SpeedLimit
, dataDateTime
, ROW_NUMBER() OVER (PARTITION BY CAST(dataDateTime AS DATE), BusId ORDER BY CurrentSpeed DESC) RowNumOrder
FROM BUS_DATA
WHERE busId = '4-3323309834' HAVING X.RowNumOrder = 1

正确的Oracle SQL写法 #

Oracle中子查询的别名不能使用AS关键字，且子查询必须明确指定别名。另外，日期转换用TRUNC(dataDateTime)替代CAST(dataDateTime AS DATE)更符合Oracle习惯（效果一致），正确写法如下：

SELECT X.*
FROM (
    SELECT
        BusID,
        BusName,
        CurrentSpeed,
        PassengersNo,
        SpeedLimit,
        dataDateTime,
        ROW_NUMBER() OVER (
            PARTITION BY TRUNC(dataDateTime), BusId
            ORDER BY CurrentSpeed DESC
        ) AS RowNumOrder
    FROM BUS_DATA 
    WHERE busId = '4-3323309834'
) X
WHERE X.RowNumOrder = 1

关键说明 #

1. 移除子查询别名前的AS：Oracle规定表别名不能用AS，列别名可以使用。
2. 明确子查询别名X：外层查询通过X.引用子查询列，避免解析错误。
3. 用TRUNC(dataDateTime)处理日期：截断时间部分保留日期，是Oracle中处理日期的常用方式。

内容的提问来源于stack exchange，提问作者Emmanuel Njorodongo