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

Question

我很难将存储过程从SQLServer转换为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

=

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

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

这是可行的。但对我来说，这听起来像是一个可怕的黑客攻击，尤其是当我在相关的表中有很多记录时。

实现这一目标的最佳方式是什么？

Answer 1

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

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

或者不同场景的许多替代方案（前n行、平局处理等）。

Answer 2

改用ROW\u 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

Answer 3

在排序依据之前执行where语句。因此，您想要的查询是“取第一行，然后按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”。您需要单独列出这些列以避免出现这种情况。