Есть ли способ заставить запрос Oracle
вести себя так, как будто он содержит предложение MySQL limit
?
В MySQL
я могу это сделать:
select *
from sometable
order by name
limit 20,10
чтобы получить строки с 21-й по 30-ю (пропустить первые 20, дать следующие 10). Строки выбираются после order by
, поэтому в действительности они начинаются с 20-го имени в алфавитном порядке.
В Oracle
единственное, что люди упоминают, это псевдостолбец rownum
, но он оценивается до order by
, что означает следующее:
select *
from sometable
where rownum <= 10
order by name
вернет случайный набор из десяти строк, упорядоченных по имени, что обычно не является тем, что мне нужно. Это также не позволяет указать смещение.
Для этого можно использовать подзапрос, например
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Просмотрите также тему О ROWNUM и ограничении результатов на сайте Oracle/AskTom для получения дополнительной информации.
Обновление: Чтобы ограничить результат как нижней, так и верхней границами, все становится немного более раздутым с
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
(Скопировано из указанной AskTom-статьи)
Обновление 2: Начиная с Oracle 12c (12.1) доступен синтаксис для ограничения строк или запуска по смещению.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Дополнительные примеры см. в этом ответе. Спасибо Krumia за подсказку.
Начиная с Oracle 12С Р1 (12.1), там такое есть ограничительного пункта строки. Он не использует привычные "лимит" синтаксис, но он может сделать работу лучше, с большим количеством опций. Вы можете найти полный синтаксис здесь.
Чтобы ответить на исходный вопрос, здесь'ы запрос:
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
(В более ранних версиях Oracle, пожалуйста, обратитесь к другим ответам на этот вопрос)
Следующие примеры были приведены из ссылке, в надежде предотвратить гниение ссылке.
Настройки ###
CREATE TABLE rownum_order_test (
val NUMBER
);
INSERT ALL
INTO rownum_order_test
SELECT level
FROM dual
CONNECT BY level <= 10;
COMMIT;
SELECT val
FROM rownum_order_test
ORDER BY val;
VAL
----------
1
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10
20 rows selected.
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;
VAL
----------
10
10
9
9
8
5 rows selected.
строк N
, если Н
<суп>й</с SUP> По строке связей, сделать все завязано строкSELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;
VAL
----------
10
10
9
9
8
8
6 rows selected.
х
% строкSELECT val
FROM rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;
VAL
----------
1
1
2
2
4 rows selected.
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;
VAL
----------
3
3
4
4
4 rows selected.
Я сделал некоторые тесты производительности для следующих подходов:
в
select * from (
select a.*, ROWNUM rnum from (
<select statement with order by clause>
) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW
Аналитическая ### в
select * from (
<select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW
в
select * from (
select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW
Таблице 10 млн записей, сортировки по ряду неиндексированных типа datetime:
Выбрать первые 10 строк заняли:
Выбор строк между 100 000 и 100,010:
Выбор строк между 9,000,000 и 9,000,010:
Аналитическое решение с одним вложенным запросом:
SELECT * FROM
(
SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
)
WHERE MyRow BETWEEN 10 AND 20;
Rank()
можно заменить на Row_Number()
, но это может вернуть больше записей, чем вы ожидаете, если есть дублирующиеся значения для имени.
В Oracle 12С (см. строку ограничительного пункта в SQL ссылку):
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Пагинация запросов с заказом очень сложно в Oracle.
Oracle предоставляет псевдостолбцом номера строки, которая возвращает число, указывающее на порядок, в котором база данных выбирает строки из таблицы или набора соединяемых представлений.
Параметр rownum псевдостолбцом, что многие люди в беду. Значение параметра rownum не жестко привязаны к счету (это распространенное заблуждение). Это может быть запутанным, когда значение параметра rownum фактически назначены. Значение параметра rownum присваивается подряд после прохождения предикатов фильтр по вопросу до объединения запросов и сортировки.
Более того, значение номера строки увеличивается на единицу только после того, как он присваивается.
Вот почему запрос в повторе не возвращает ни одной строки:
select *
from (select *
from some_table
order by some_column)
where ROWNUM <= 4 and ROWNUM > 1;
Первую строку результата запроса не проходит параметр rownum > 1 предикат, поэтому параметр rownum не прирастить до 2. По этой причине значение параметра rownum возвращает больше 1, следовательно, запрос не возвращает ни одной строки.
Правильно определен запрос должен выглядеть так:
select *
from (select *, ROWNUM rnum
from (select *
from skijump_results
order by points)
where ROWNUM <= 4)
where rnum > 1;
Узнайте больше о пагинации запросов в моих статьях на Vertabelo блог:
Меньше инструкций Select. Кроме того, меньше затрат. Кредиты: [email protected]
SELECT *
FROM (SELECT t.*,
rownum AS rn
FROM shhospede t) a
WHERE a.rn >= in_first
AND a.rn <= in_first;
Как расширение принято отвечать Оракул внутренне использует функции функции row_number/ранг.
Смещение выборки синтаксис-это синтаксис сахара.
Это может быть отмечено с помощью DBMS_UTILITY.EXPAND_SQL_TEXT
процедура:
Подготовка образца:
CREATE TABLE rownum_order_test (
val NUMBER
);
INSERT ALL
INTO rownum_order_test
SELECT level
FROM dual
CONNECT BY level <= 10;
COMMIT;
Запрос:
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;
регулярный:
SELECT "A1"."VAL" "VAL"
FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber"
FROM "ROWNUM_ORDER_TEST" "A2") "A1"
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;
Получение расширенной SQL-текст:
declare
x VARCHAR2(1000);
begin
dbms_utility.expand_sql_text(
input_sql_text => '
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY',
output_sql_text => x);
dbms_output.put_line(x);
end;
/
Со связями
расширяется как ранг
:
declare
x VARCHAR2(1000);
begin
dbms_utility.expand_sql_text(
input_sql_text => '
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES',
output_sql_text => x);
dbms_output.put_line(x);
end;
/
SELECT "A1"."VAL" "VAL"
FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank"
FROM "ROWNUM_ORDER_TEST" "A2") "A1"
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC
и смещения:
declare
x VARCHAR2(1000);
begin
dbms_utility.expand_sql_text(
input_sql_text => '
SELECT val
FROM rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
output_sql_text => x);
dbms_output.put_line(x);
end;
/
SELECT "A1"."VAL" "VAL"
FROM (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber"
FROM "ROWNUM_ORDER_TEST" "A2") "A1"
WHERE "A1"."rowlimit_$$_rownumber"<=CASE WHEN (4>=0) THEN FLOOR(TO_NUMBER(4))
ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4
ORDER BY "A1"."rowlimit_$_0"
Как мне объяснили в настоящей статьи, стандарта SQL:2008 предоставляет следующий синтаксис, чтобы ограничить результирующий набор в SQL:
SELECT
title
FROM
post
ORDER BY
id DESC
FETCH FIRST 50 ROWS ONLY
До версии 12С, для получения топ-N записей, вы должны использовать производную таблицу и номера строки псевдостолбцом:
SELECT *
FROM (
SELECT
title
FROM
post
ORDER BY
id DESC
)
WHERE ROWNUM <= 50
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5
больше значения узнать
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID>5
меньше значения узнать
select * FROM (SELECT
ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID,
FROM EMP ) EMP where ROWID=5
Если вы не в Oracle 12С, вы можете использовать первые N запросов, как показано ниже.
SELECT *
FROM
( SELECT rownum rnum
, a.*
FROM sometable a
ORDER BY name
)
WHERE rnum BETWEEN 10 AND 20;
Вы можете даже переместить из пункта в пункт следующим образом
WITH b AS
( SELECT rownum rnum
, a.*
FROM sometable a ORDER BY name
)
SELECT * FROM b
WHERE rnum BETWEEN 10 AND 20;
Здесь на самом деле мы создаем встроенный просмотр и переименование как параметр rownum rnum. Вы можете использовать rnum в основном запросе в качестве критерия фильтра.
Для каждой строки, возвращаемой запросом, в псевдостолбцом параметр rownum возвращает число, указывающее порядок, в котором Оракул выбирает строки из таблицы или набора соединяемых строк. Первая выбранная строка имеет номера строки на 1, второй 2, и так далее.
SELECT * FROM sometable1 so
WHERE so.id IN (
SELECT so2.id from sometable2 so2
WHERE ROWNUM <=5
)
AND ORDER BY so.somefield AND ROWNUM <= 100
Я реализовал это в оракул
сервер 11.2.0.1.0
Я'V начал подготовку для Oracle 1z0-047 экзамен, проверяется 12С Пока усердно готовлюсь к нему, я наткнулся на совершенствование 12С, известный как 'принести первый' Это позволяет выбирать строки /лимит строк в соответствии с вашего удобства. Есть несколько вариантов с этим
- FETCH FIRST n ROWS ONLY
- OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
- n % rows via FETCH FIRST N PERCENT ROWS ONLY
Пример:
Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY
(не проверено) может подойти что-то вроде этого
WITH
base AS
(
select * -- get the table
from sometable
order by name -- in the desired order
),
twenty AS
(
select * -- get the first 30 rows
from base
where rownum < 30
order by name -- in the desired order
)
select * -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name -- in the desired order
Существует также аналитическая функция rank, которую можно использовать для упорядочивания по.
Же, как и выше с исправлениями. Работает, но наверняка не очень.
WITH
base AS
(
select * -- get the table
from sometable
order by name -- in the desired order
),
twenty AS
(
select * -- get the first 30 rows
from base
where rownum <= 30
order by name -- in the desired order
)
select * -- then get rows 21 .. 30
from twenty
where rownum < 20
order by name -- in the desired order
Честно говоря, лучше использовать вышеперечисленные ответы.