ORA-00907:缺失右括号错误排查——SQL查询语句问题求助

这个错误看起来是括号不匹配导致的，但实际是标量子查询中ORDER BY的语法位置错误引发的Oracle解析失败，让我们一步步拆解问题：

问题根源 #

你的第一个标量子查询直接在WHERE子句后加了ORDER BY：

(select PRE_DESIG_ID FROM AUTHORIZATION WHERE PROJECT_ID = 5 and PRE_DESIG_ID =48 and ROWNUM=1 order by ID DESC)

在Oracle中，直接的标量子查询（未嵌套内层子查询的单层级子查询）不允许包含ORDER BY子句——虽然你的括号数量完全正确，但Oracle解析器会因为这个语法不符合规则，误报为"缺失右括号"。

另外，就算语法允许，这个写法的逻辑也有问题：ROWNUM是在结果集生成后、排序前分配的，所以ROWNUM=1会先取到未排序的随机一行，再执行排序，这和你想要"取ID最新的那一行"的需求完全不符。

修正后的SQL #

把第一个子查询改成和第二个一样的嵌套结构，将ORDER BY放到内层子查询中，在外层过滤ROWNUM=1，这样既符合Oracle语法规则，逻辑也能满足需求：

SELECT 
    (SELECT PRE_DESIG_ID 
     FROM (SELECT PRE_DESIG_ID FROM AUTHORIZATION 
           WHERE PROJECT_ID = 5 AND PRE_DESIG_ID = 48 
           ORDER BY ID DESC) 
     WHERE ROWNUM = 1) AS PERPARED_BY,
    (SELECT PRE_LAST_DATE 
     FROM (SELECT PRE_LAST_DATE FROM AUTHORIZATION 
           WHERE PROJECT_ID = 5 AND PRE_DESIG_ID = 48 
           ORDER BY ID DESC) 
     WHERE ROWNUM = 1) AS PRE_END_DT 
FROM AUTHORIZATION au 
LEFT JOIN PROJECT p ON au.PROJECT_ID = p.PROJECT_ID 
LEFT JOIN DESIGNATION d ON au.AU_DESIG_ID = d.DESIGID;

额外优化建议 #

如果你使用的是Oracle 12c及以上版本，可以用FETCH FIRST 1 ROW ONLY替代ROWNUM的嵌套写法，代码更简洁直观：

SELECT 
    (SELECT PRE_DESIG_ID 
     FROM AUTHORIZATION 
     WHERE PROJECT_ID = 5 AND PRE_DESIG_ID = 48 
     ORDER BY ID DESC 
     FETCH FIRST 1 ROW ONLY) AS PERPARED_BY,
    (SELECT PRE_LAST_DATE 
     FROM AUTHORIZATION 
     WHERE PROJECT_ID = 5 AND PRE_DESIG_ID = 48 
     ORDER BY ID DESC 
     FETCH FIRST 1 ROW ONLY) AS PRE_END_DT 
FROM AUTHORIZATION au 
LEFT JOIN PROJECT p ON au.PROJECT_ID = p.PROJECT_ID 
LEFT JOIN DESIGNATION d ON au.AU_DESIG_ID = d.DESIGID;

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