Oracle子查询ORDER BY引发ORA-00907/ORA-00936错误求助

问题根源 #

在Oracle中，直接在单行子查询里同时使用ROWNUM = 1和ORDER BY会触发ORA-00907: missing right parenthesis错误——这是Oracle语法规则限制导致的。更重要的是，就算语法允许，ROWNUM是在排序前就分配的，这样取到的结果也不是你想要的最早里程记录，逻辑上也不对。

正确的写法 #

你需要把排序逻辑放到内层嵌套子查询中，先完成排序，再在外层用ROWNUM = 1获取第一条记录。以下是修正后的完整查询：

SELECT 
    /* DATE OF THE ROUTE */ {GPS}.[DateTime], 
    /* ROUTE DESCRIPTION */ {Route}.[Description], 
    /* NAME OF THE DRIVER */ {Driver}.[Name], 
    /* VEHICLE LICENSE PLATE */ {Vehicle}.[Registration], 
    /* QUANTITY OF STOPS */ 
    (SELECT COUNT({RouteStop}.[RouteId]) FROM {RouteStop} WHERE {RouteStop}.[RouteId] = {GPS}.[RouteId]) AS StopCount, 
    /* AMOUNT OF FUEL */ 
    (SELECT SUM(FUEL.[Value]) FROM {GPS} FUEL WHERE {GPS}.[RouteId] = FUEL.[RouteId] AND FUEL.[EventTypeId] = 23) FuelAmount, 
    /* Event Fuel */ 
    /* ROUTE STARTDATETIME */ {GPS}.[DateTime] AS ROUTESTARTDATETIME, 
    /* ROUTE ENDDATETIME */ 
    (SELECT ROUTEENDDATETIME.[DateTime] FROM {GPS} ROUTEENDDATETIME WHERE {GPS}.[RouteId] = ROUTEENDDATETIME.[RouteId] AND ROUTEENDDATETIME.[EventTypeId] = 5 /* Event Route Completed */ AND ROWNUM = 1) AS ROUTEEND, 
    /* INITIAL MILEAGE */ 
    (SELECT MILEAGE_VAL FROM (
        SELECT MILEAGEBEGIN.[Value] AS MILEAGE_VAL 
        FROM {GPS} MILEAGEBEGIN 
        WHERE {GPS}.[RouteId] = MILEAGEBEGIN.[RouteId] AND MILEAGEBEGIN.[EventTypeId] = 21 /* Event Mileage */ 
        ORDER BY MILEAGEBEGIN.[DateTime] ASC
    ) WHERE ROWNUM = 1) AS INITIALMILEAGE 
FROM {GPS} 
INNER JOIN {Route} ON {GPS}.[RouteId] = {Route}.[Id] 
INNER JOIN {Driver} ON {GPS}.[DriverId] = {Driver}.[Id] 
INNER JOIN {Availability} ON {Driver}.[Id] = {Availability}.[DriverId] 
INNER JOIN {Vehicle} ON {Availability}.[VehicleId] = {Vehicle}.[Id] 
WHERE {GPS}.[EventTypeId] = 3 /* Event RouteStarted */

你的尝试写法为什么报错？ #

你尝试的代码存在语法错误：在主查询的SELECT列表里额外加了一个SELECT关键字，而且嵌套子查询的结构混乱。正确的单行子查询应该是一个被括号完整包裹的查询块，不需要额外的SELECT前缀。

补充优化方案（Oracle 12c+） #

如果你的Oracle版本是12c及以上，可以用FETCH FIRST 1 ROW ONLY替代ROWNUM写法，语法更简洁清晰：

/* INITIAL MILEAGE */ 
(SELECT MILEAGEBEGIN.[Value] 
 FROM {GPS} MILEAGEBEGIN 
 WHERE {GPS}.[RouteId] = MILEAGEBEGIN.[RouteId] AND MILEAGEBEGIN.[EventTypeId] = 21 /* Event Mileage */ 
 ORDER BY MILEAGEBEGIN.[DateTime] ASC
 FETCH FIRST 1 ROW ONLY) AS INITIALMILEAGE

这种写法不需要嵌套子查询，可读性更好。

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