如何正确使用Oracle的ORDER BY和ROWNUM？

Question

我正在努力将 SQL Server 中的存储过程转换为 Oracle，以使我们的产品与之兼容。

我有一些查询语句，根据时间戳返回某些表的最新记录：

SQL Server：

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

=> 这将返回我最近的记录

但是 Oracle：

SELECT *
FROM raceway_input_labo 
WHERE  rownum <= 1
ORDER BY t_stamp DESC

=> 这将返回最旧的记录（可能取决于索引），而不考虑ORDER BY语句！

我以这种方式封装了Oracle查询以满足我的要求:

SELECT * 
FROM 
    (SELECT *
     FROM raceway_input_labo 
     ORDER BY t_stamp DESC)
WHERE  rownum <= 1

它可以工作。但对我来说，这听起来像是一个可怕的黑客攻击，特别是如果涉及的表中有很多记录。

那么，最好的方法是什么？

- Larry

---

关于 ROWNUM 和结果限制 - John Woo

你在上一个查询中的操作是正确的。你选择了已排序记录列表的第一行。这只是查询封装。 - araknoid

这在手册中有明确的文档记录：http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#i1006297 - user330315

@a_horse_with_no_name的意思是，这个404错误需要有清晰的文档记录。 - imperfectgrist

谢谢。Oracle将他们所有的URL更改为手册。最新链接是: https://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns009.htm#SQLRF00255 - user330315

Answer 1

where语句在order by之前执行。因此，您想要的查询是说“取第一行，然后按t_stamp倒序排序”。这不是您的意图。

子查询方法是在Oracle中执行此操作的正确方法。

如果您想要适用于两个服务器的版本，则可以使用：

select ril.*
from (select ril.*, row_number() over (order by t_stamp desc) as seqnum
      from raceway_input_labo ril
     ) ril
where seqnum = 1

外层的*会在最后一列返回"1"。为了避免这种情况，您需要逐个列出列名。

- Gordon Linoff

---

这个代码是不是遍历了整个 raceway_input_labo 表并分配行号，然后再进行筛选？如果是的话，那么在处理大表时会不会出现问题？ - Hasan Can Saral

Answer 2

使用ROW_NUMBER()代替ROWNUM。 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

- Art

---

ROWNUM 可能比 ROW_NUMBER() 更快，所以是否应该使用其中一个取决于许多因素。 - David Faber

抱歉，我误操作点了踩！不幸的是，我现在无法撤回它。 - Athafoud

Answer 3

自从Oracle 12c以来，我们现在有了行限制子句，可以完全做到这一点。

SELECT *
FROM raceway_input_labo 
ORDER BY t_stamp DESC
FETCH FIRST ROW ONLY

或许有许多替代方案可适用于不同场景（前n行、并列处理等）。

- user3067860

Answer 4

在上面的评论中记录了几个与此相关的设计问题。简而言之，在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')  

将大大减少结果。然后您可以按ORDER BY排序，甚至执行外部查询以限制行数。

此外，我认为TOAD有一个限制行数的功能；但是，不确定它是否在Oracle实际查询中进行限制。不确定。

- maxweber

Answer 5

我建议在这种情况下使用的替代方法是使用MAX(t_stamp)来获取最新的行...例如。

select t.* from raceway_input_labo t
where t.t_stamp = (select max(t_stamp) from raceway_input_labo) 
limit 1

我的编程模式偏好（也许）- 可靠，通常表现得比尝试从排序列表中选择第一行更好 - 同时意图更明确可读。
希望这可以帮到你...

SQLer

- SQLer

---

Oracle中没有限制。你在提出问题。 - philipxy