Есть ли способ заставить запрос Oracle
вести себя так, как будто он содержит условие MySQL limit
?
В MySQL
я могу это сделать:
select *
from sometable
order by name
limit 20,10
чтобы получить 21-е и 30-е ряды (пропустите первые 20, дайте следующие 10). Строки выбираются после order by
, поэтому он действительно начинается с 20-го имени в алфавитном порядке.
В Oracle
единственное, о чем люди rownum
псевдо-столбец rownum
, но он оценивается перед order by
, что означает:
select *
from sometable
where rownum <= 10
order by name
вернет случайный набор из десяти строк, упорядоченных по имени, что обычно не является тем, что я хочу. Он также не позволяет указывать смещение.
Начиная с Oracle 12c R1 (12.1) существует предложение ограничения строки. Он не использует знакомый синтаксис LIMIT
, но он может улучшить работу с большим количеством опций. Здесь вы можете найти полный синтаксис .
Чтобы ответить на исходный вопрос, вот запрос:
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.
N
строки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
, если строка N
th имеет связи, получить все связанные строки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.
x
% строк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 *
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;
Подробнее см. этот ответ. Спасибо Крумии за подсказку.
Я провел несколько тестов производительности для следующих подходов:
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:
BETWEEN
- это просто сокращение для >= AND <=
( stackoverflow.com/questions/4809083/between-clause-versus-and )
Аналитическое решение с одним вложенным запросом:
SELECT * FROM
(
SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
)
WHERE MyRow BETWEEN 10 AND 20;
Rank()
может быть заменен на Row_Number()
, но может возвращать больше записей, чем вы ожидаете, если есть повторяющиеся значения для имени.
В Oracle 12c (см. предложение ограничения строки в ссылка SQL):
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
LIMIT
в SQL: 2008, им пришлось взять листок из книги Microsoft и нарушить стандарт.
Запросы разбивки на страницы с упорядочением действительно сложны в Oracle.
Oracle предоставляет псевдокоманду ROWNUM, которая возвращает номер, указывающий порядок, в котором база данных выбирает строку из таблицы или набора объединенных представлений.
ROWNUM - это псевдоколонка, в которую попадают многие люди. Значение ROWNUM не назначается подряд подряд (это общее недоразумение). Это может сбивать с толку, когда действительно назначено значение ROWNUM. Значение ROWNUM присваивается строке после передачи предикатов фильтра запроса, но до агрегации или сортировки запроса.
Кроме того, значение ROWNUM увеличивается только после его назначения.
Вот почему запрос followin не возвращает строк:
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;
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
ROW_NUMBER()
как ROW_NUMBER()
основе ROW_NUMBER()
уже было опубликовано Ли Риффелем. В зависимости есть синтаксические ошибки в показанном коде.
Если вы не используете Oracle 12C, вы можете использовать запрос TOP 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 в основном запросе в качестве критериев фильтра.
ORDER BY
и rownum
отдельности. По сути, я создал подзапрос с предложением ORDER BY
.
rownum
должен находиться за пределами подзапроса.
I'v начал подготовку к экзамену Oracle 1z0-047, проверенному против 12c Во время подготовки к этому я наткнулся на расширение 12c, известное как "FETCH FIRST" Это позволяет вам извлекать строки/ограничивать строки в соответствии с вашим удобством. Доступно несколько опций
- 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
Для каждой строки, возвращаемой запросом, псевдоколонка ROWNUM возвращает номер, указывающий порядок, в котором Oracle выбирает строку из таблицы или набора соединенных строк. Выбранная первая строка имеет ROWNUM 1, вторая - 2 и так далее.
SELECT * FROM sometable1 so
WHERE so.id IN (
SELECT so2.id from sometable2 so2
WHERE ROWNUM <=5
)
AND ROWNUM <= 100
Я реализовал это на сервере oracle
11.2.0.1.0
Я использовал Oracle SQL Developer следующую инструкцию для извлечения первых 10 строк и работает для меня:
SELECT * FROM <table name> WHERE ROWNUM < = 10 ORDER BY <column name>;
В оракуле
SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY;
10
10
9
9
8
Выбрано 5 строк.
SQL >
(непроверенный) что-то вроде этого может выполнить работу
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
Существует также ранг аналитической функции, который вы можете использовать для заказа.
То же, что и выше, с исправлениями. Работает, но определенно не очень.
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
Честно говоря, лучше использовать приведенные выше ответы.