SQL 如何正确使用 Oracle ORDER BY 和 ROWNUM？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15091849/
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
How to use Oracle ORDER BY and ROWNUM correctly?
提问by Larry
I am having a hard time converting stored procedures from SQL Server to Oracle to have our product compatible with it.
我很难将存储过程从 SQL Server 转换为 Oracle 以使我们的产品与之兼容。
I have queries which returns the most recent record of some tables, based on a timestamp :
我有基于时间戳返回某些表的最新记录的查询：
SQL Server:
SQL 服务器：
SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC
=> That will returns me the most recent record
=> 这将返回我最近的记录
But Oracle:
但是甲骨文：
SELECT *
FROM raceway_input_labo
WHERE rownum <= 1
ORDER BY t_stamp DESC
=> That will returns me the oldest record (probably depending on the index), regardless the ORDER BYstatement!
=> 无论ORDER BY语句如何，这都会返回最旧的记录（可能取决于索引）！
I encapsulated the Oracle query this way to match my requirements:
我以这种方式封装了 Oracle 查询以符合我的要求：
SELECT *
FROM
(SELECT *
FROM raceway_input_labo
ORDER BY t_stamp DESC)
WHERE rownum <= 1
and it works. But it sounds like a horrible hack to me, especially if I have a lot of records in the involved tables.
它有效。但这对我来说听起来像是一个可怕的黑客，特别是如果我在涉及的表中有很多记录。
What is the best way to achieve this ?
实现这一目标的最佳方法是什么？
采纳答案by Gordon Linoff
The wherestatement gets executed beforethe order by. So, your desired query is saying "take the first row and then order it byt_stampdesc". And that is not what you intend.
该where声明被执行之前的order by。所以，你想要的查询是说“取第一行，然后按t_stampdesc排序”。这不是你想要的。
The subquery method is the proper method for doing this in Oracle.
子查询方法是在 Oracle 中执行此操作的正确方法。
If you want a version that works in both servers, you can use:
如果你想要一个在两台服务器上都能运行的版本，你可以使用：
select ril.*
from (select ril.*, row_number() over (order by t_stamp desc) as seqnum
from raceway_input_labo ril
) ril
where seqnum = 1
The outer *will return "1" in the last column. You would need to list the columns individually to avoid this.
外部*将在最后一列返回“1”。您需要单独列出列以避免这种情况。
回答by Art
Use ROW_NUMBER()instead. ROWNUMis a pseudocolumn and ROW_NUMBER()is a function. You can read about difference between them and see the difference in output of below queries:
使用ROW_NUMBER()来代替。ROWNUM是一个伪列并且ROW_NUMBER()是一个函数。您可以阅读它们之间的差异并查看以下查询的输出差异：
SELECT * FROM (SELECT rownum, deptno, ename
FROM scott.emp
ORDER BY deptno
)
WHERE rownum <= 3
/
ROWNUM DEPTNO ENAME
---------------------------
7 10 CLARK
14 10 MILLER
9 10 KING
SELECT * FROM
(
SELECT deptno, ename
, ROW_NUMBER() OVER (ORDER BY deptno) rno
FROM scott.emp
ORDER BY deptno
)
WHERE rno <= 3
/
DEPTNO ENAME RNO
-------------------------
10 CLARK 1
10 MILLER 2
10 KING 3
回答by maxweber
Documented couple of design issues with this in a comment above. Short story, in Oracle, you need to limit the results manually when you have large tables and/or tables with same column names (and you don't want to explicit type them all out and rename them all). Easy solution is to figure out your breakpoint and limit that in your query. Or you could also do this in the inner query if you don't have the conflicting column names constraint. E.g.
在上面的评论中记录了几个设计问题。简而言之，在 Oracle 中，当您有大表和/或具有相同列名的表时，您需要手动限制结果（并且您不想显式地将它们全部键入并全部重命名）。简单的解决方案是找出您的断点并在您的查询中限制它。或者，如果您没有冲突的列名约束，您也可以在内部查询中执行此操作。例如
WHERE m_api_log.created_date BETWEEN TO_DATE('10/23/2015 05:00', 'MM/DD/YYYY HH24:MI')
AND TO_DATE('10/30/2015 23:59', 'MM/DD/YYYY HH24:MI')
will cut down the results substantially. Then you can ORDER BY or even do the outer query to limit rows.
将大大减少结果。然后您可以 ORDER BY 甚至执行外部查询来限制行。
Also, I think TOAD has a feature to limit rows; but, not sure that does limiting within the actual query on Oracle. Not sure.
另外，我认为 TOAD 具有限制行的功能；但是，不确定这是否会限制在 Oracle 的实际查询中。没有把握。
回答by SQLer
An alternate I would suggest in this use case is to use the MAX(t_stamp) to get the latest row ... e.g.
在这个用例中我建议的另一种方法是使用 MAX(t_stamp) 来获取最新的行......例如
select t.* from raceway_input_labo t
where t.t_stamp = (select max(t_stamp) from raceway_input_labo)
limit 1
My coding pattern preference (perhaps) - reliable, generally performs at or better than trying to select the 1st row from a sorted list - also the intent is more explicitly readable.
Hope this helps ...
我的编码模式偏好（也许） - 可靠，通常执行或优于尝试从排序列表中选择第一行 - 并且意图更明确可读。
希望这可以帮助 ...
SQLer
查询器
