如何正确使用 OracleORDERBY 和 ROWNUM？

我有一个困难的时间转换存储过程从 SQLServer 到甲骨文有我们的产品兼容它。

我有一些查询，它们根据时间戳返回一些表的最新记录:

SQLServer:

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

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

但是 甲骨文:

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

而且成功了。但对我来说，这听起来像一个可怕的黑客，特别是如果我有很多记录，在涉及的表。

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

338502 次浏览
小开
最佳答案

where语句执行 之前和 order by。因此，您需要的查询是“ 取第一行，然后按顺序排列 t_stamp Desc”。这不是你的本意。

子查询方法是在 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”。为了避免这种情况，您需要单独列出这些列。

小开

使用 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
小开

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

将大大减少结果。然后您可以 ORDERBY 或者甚至执行外部查询来限制行。

另外，我认为 TOAD 有一个限制行的特性; 但是，不能确定它是否限制了对 Oracle 的实际查询。不确定。

小开

在这个用例中，我建议使用 MAX (t _ 戳)来获取最新的一行... ... 例如。

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

我的编码模式偏好(也许)——可靠，通常执行得比试图从排序列表中选择第一行要好或者更好——而且意图更加明确可读。
希望这个能帮上忙。

SQLer

小开

从 Oracle 12c 开始，我们现在有了 行限制条款行限制条款，它完全做到了这一点。

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

或者许多 选择用于不同的场景(前 n 行，领带处理等)。


提交答案