Как ограничить количество строк, возвращаемых запросом Oracle после упорядочивания?

Есть ли способ заставить запрос 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

вернет случайный набор из десяти строк, упорядоченных по имени, что обычно не то, что я хочу. Это также не позволяет указывать смещение.

вопрос задан 22.01.2009
Mathieu Longtin
5572 репутация

13 ответов


  • 722 рейтинг

    Вы можете использовать подзапрос для этого, как

    select *
    from  
    ( select * 
      from emp 
      order by sal desc ) 
    where ROWNUM <= 5;
    

    Посмотрите также тему О ROWNUM и ограничении результатов в Oracle / AskTom для получения дополнительной информации.

    Обновление : Чтобы ограничить результат как нижними, так и верхними границами, все становится немного более раздутым с

    select * from 
    ( select a.*, ROWNUM rnum from 
      (  ) 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;
    

    См. этот ответ для большего количества примеров. Спасибо Крумии за подсказку.

    ответ дан Kosi2801, с репутацией 15616, 22.01.2009
  • 346 рейтинг

    Начиная с 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 -й строки имеет связи, получить все связанные строки

    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.
    
    ответ дан Krumia, с репутацией 12239, 26.09.2014
  • 168 рейтинг

    Я провел тестирование производительности для следующих подходов:

    Asktom

    select * from (
      select a.*, ROWNUM rnum from (
        
    ответ дан zeldi, с репутацией 4133, 30.06.2011
  • 53 рейтинг

    Аналитическое решение только с одним вложенным запросом:

    SELECT * FROM
    (
       SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
    ) 
    WHERE MyRow BETWEEN 10 AND 20;
    

    Rank() можно заменить на Row_Number(), но может вернуть больше записей, чем вы ожидаете, если для имени есть повторяющиеся значения.

    ответ дан Leigh Riffel, с репутацией 4027, 23.01.2009
  • 27 рейтинг

    В Oracle 12c (см. Пункт ограничения строки в Справочник по SQL ):

    SELECT * 
    FROM sometable
    ORDER BY name
    OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
    
    ответ дан beldaz, с репутацией 2736, 24.09.2013
  • 10 рейтинг

    Пагинационные запросы с упорядочением действительно сложны в Oracle

    Oracle предоставляет псевдостолбец ROWNUM, который возвращает число, указывающее порядок, в котором база данных выбирает строку из таблицы или набора объединенных представлений.

    ROWNUM - псевдостолбец, который ставит многих людей в беду. Значение ROWNUM не всегда назначается строке (это распространенное недоразумение). Это может сбивать с толку, когда значение ROWNUM фактически назначается Значение ROWNUM присваивается строке после прохождения предикатов фильтра запроса, но до агрегации или сортировки запроса .

    Более того, значение ROWNUM увеличивается только после его назначения.

    Вот почему следующий запрос не возвращает строк:

     select * 
     from (select *
           from some_table
           order by some_column)
     where ROWNUM <= 4 and ROWNUM > 1; 
    

    Первая строка результата запроса не проходит ROWNUM & gt; 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 blog:

    ответ дан Bartek, с репутацией 149, 12.04.2014
  • 7 рейтинг

    Меньше операторов SELECT. Кроме того, меньше потребляет производительность. Кредиты для: anibal @ upf. br

    SELECT *
        FROM   (SELECT t.*,
                       rownum AS rn
                FROM   shhospede t) a
        WHERE  a.rn >= in_first
        AND    a.rn <= in_first;
    
    ответ дан Felipe Q. Giovanoni, с репутацией 87, 2.03.2011
  • 3 рейтинг

    Если вы не используете 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 в основном запросе в качестве критерия фильтрации.

    ответ дан sandi, с репутацией 60, 8.12.2016
  • 3 рейтинг
    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
    
    ответ дан Mehul Akabari, с репутацией 69, 12.04.2017
  • 1 рейтинг

    Я начал подготовку к экзамену 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
    
    ответ дан arjun gaur, с репутацией 179, 1.06.2016
  • -2 рейтинг

    В оракуле

    SELECT val FROM   rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY;
    

    VAL

        10
        10
         9
         9
         8
    

    5 строк выбрано.

    SQL & gt;

    ответ дан Rakesh, с репутацией 1740, 20.08.2014
  • -4 рейтинг

    (не проверено) что-то вроде этого может сделать работу

    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
    

    Существует также ранг аналитической функции, который вы можете использовать для упорядочения.

    ответ дан EvilTeach, с репутацией 19605, 22.01.2009
  • -5 рейтинг

    То же, что и выше с исправлениями. Работает, но определенно не красиво.

       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
    

    Честно говоря, лучше использовать ответы выше.

    ответ дан stimepy, с репутацией 23, 18.11.2010