Oracle：使用 WHERE ROWNUM = 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12245607/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: using WHERE ROWNUM = 1
提问by Skulmuk
chaps and chapettes
chaps and chapettes
Just a quick question. I need to return only one row from a stored proc., but no matter where I place the WHERE clause, I get errors. Can somebody take a look at the (cut-down due to sheer length) code and let me know where it should go, please?
只是一个简单的问题。我只需要从存储过程中返回一行，但无论我将 WHERE 子句放在哪里，都会出错。有人可以看看（由于长度太长而减少）代码并让我知道它应该去哪里，好吗？
SELECT **values**
INTO **variables**
FROM **table**
_WHERE ROWNUM = 1_
INNER JOIN **other table**
ON **join target**
ORDER BY **sort criteria**;
_WHERE ROWNUM = 1_
Thanks
谢谢
回答by IvoTops
I believe this is the way to structure rownum queries
我相信这是构造 rownum 查询的方法
SELECT * FROM
INTO **Variables * *
( SELECT * FROM X
WHERE Y
ORDER BY Z
)
WHERE ROWNUM = 1;
回答by Jeffrey Kemp
You were almost correct. You put the WHERE clause after the JOINs, but before the ORDER BY.
你几乎是正确的。您将 WHERE 子句放在 JOIN 之后，但在 ORDER BY 之前。
SELECT **values**
INTO **variables**
FROM **table**
INNER JOIN **other table**
ON **join target**
_WHERE ROWNUM = 1_
ORDER BY **sort criteria**;
However, this won't do what you might think - the ORDER BY is evaluated AFTER the where clause; which means this will just pick the first record it finds (that satisfies the join criteria), and will then sort that row (which obviously is a no-op).
然而，这不会像你想象的那样 - ORDER BY 是在 where 子句之后计算的；这意味着这将只选择它找到的第一条记录（满足连接条件），然后对该行进行排序（这显然是一个空操作）。
The other answers (e.g. IvoTops') give ideas of how to get the first record according to the sort criteria.
其他答案（例如 IvoTops 的）给出了如何根据排序标准获取第一条记录的想法。
回答by ypercube??
SELECT **values**
INTO **variables**
FROM
( SELECT **values**
, ROW_MUMBER() OVER (ORDER BY **sort criteria**) AS rn
FROM **table**
INNER JOIN **other table**
ON **join target**
) tmp
WHERE rn = 1 ;
Check also this blog post: Oracle: ROW_NUMBER() vs ROWNUM
另请查看此博客文章：Oracle：ROW_NUMBER() 与 ROWNUM
