Oracle rownum ORA-00923: 未找到要求的 FROM 关键字 (不积跬步,无以至千里)

最新推荐文章于 2022-10-14 10:00:31 发布

原创最新推荐文章于 2022-10-14 10:00:31 发布 · 1.6w 阅读

1 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

51 篇文章

订阅专栏

本文详细解释了在Oracle环境中如何使用ROWNUM替代TOP功能，通过提供正确的SQL查询示例，帮助解决未找到要求的FROM关键字的错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT TOP 1 B.EMPLOYEEID, B.TRUENAME FROM AM_APPROVE_LIST A, PSNACCOUNT B, PSNACCOUNT C
WHERE A.APPROVER_ID = B.FLOWER_EMP_ID AND C.FLOWER_EMP_ID = A.EMP_ID

ORDER BY APPROVER_SEQUENCE DESC

在Oracle内执行

ErrorMessage:ORA-00923: 未找到要求的 FROM 关键字

换到Oracle中不支持Top的写法,那么rownum怎么用

SELECT B.EMPLOYEEID, B.TRUENAME FROM AM_APPROVE_LIST A, PSNACCOUNT B, PSNACCOUNT C
WHERE A.APPROVER_ID = B.FLOWER_EMP_ID AND C.FLOWER_EMP_ID = A.EMP_ID
AND ROWNUM<=1 ORDER BY APPROVER_SEQUENCE DESC

上面这种是不对

正确的写法是

select EMPLOYEEID,TRUENAME FROM

(SELECT B.EMPLOYEEID, B.TRUENAME FROM AM_APPROVE_LIST A, PSNACCOUNT B, PSNACCOUNT C
WHERE A.APPROVER_ID = B.FLOWER_EMP_ID AND C.FLOWER_EMP_ID = A.EMP_ID
ORDER BY APPROVER_SEQUENCE DESC) WHERE ROWNUM<=1

单表查询的话

SELECT EMP_ID,APPROVER_SEQUENCE,APPROVER_ID FROM AM_APPROVE_LIST ORDER BY APPROVER_SEQUENCE DESC

SELECT EMP_ID,APPROVER_SEQUENCE,APPROVER_ID FROM AM_APPROVE_LIST WHERE ROWNUM<=1 ORDER BY APPROVER_SEQUENCE DESC 错误的

要用嵌套

select EMP_ID,APPROVER_SEQUENCE,APPROVER_ID from
(SELECT EMP_ID,APPROVER_SEQUENCE,APPROVER_ID FROM AM_APPROVE_LIST ORDER BY APPROVER_SEQUENCE DESC) WHERE ROWNUM<=1 正确的