Как правильно использовать Oracle ORDER BY и ROWNUM?
Мне трудно преобразовать хранимые процедуры из SQL Server в Oracle, чтобы наш продукт был совместим с ним.
У меня есть запросы, которые возвращают самую последнюю запись некоторых таблиц, основанную на отметке времени:
SQL Server:
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
и это работает. Но для меня это звучит ужасно, особенно если у меня много записей в таблицах.
Каков наилучший способ достичь этого?
6 ответов
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 12c, у нас есть предложения, ограничивающие строки, которые делают именно это.
SELECT *
FROM raceway_input_labo
ORDER BY t_stamp DESC
FETCH FIRST ROW ONLY
Или множество альтернатив для разных сценариев (первые n строк, обработка связей и т. Д.).
Альтернативный вариант, который я хотел бы предложить в этом случае, заключается в использовании MAX(t_stamp) для получения последней строки... например
select t.* from raceway_input_labo t
where t.t_stamp = (select max(t_stamp) from raceway_input_labo)
limit 1
Мои предпочтения шаблона кодирования (возможно) - надежные, обычно выполняются на уровне или лучше, чем при попытке выбрать 1-ую строку из отсортированного списка, - также цель более четко читаема.
Надеюсь это поможет...
SQLer
Документированная пара вопросов дизайна с этим в комментарии выше. Коротко говоря, в 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. Точно сказать не могу.
Просто используйте rownum, как показано ниже
select *
from (select t.*
from raceway_input_labo ril
order by t_stamp desc
)
where rownum = 1