Как ограничить количество строк в sql
Перейти к содержимому

Как ограничить количество строк в sql

  • автор:

Ограничение числа возвращаемых строк

Требуется ограничить число возвращаемых запросом строк. Порядок не имеет значения; подойдут любые n строк.

Решение

В Oracle ограничение на количество возвращаемых строк накладывается с помощью функции ROWNUM в предикате WHERE:

select e.* from emp e where rownum  
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- ---------- --- --------- --- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 

Обсуждение

Для ограничения числа возвращаемых строк в Oracle приходится использовать функцию ROWNUM, возвращающую порядковый номер каждой строки результирующего множества (возвращающую, начиная с 1, величину).

Рассмотрим, то происходит при использовании ROWNUM

  1. Oracle выполняет запрос.
  2. Oracle извлекает первую строку и называет ее строкой номер 1.
  3. Номер строки больше 5? Если нет, Oracle возвращает строку, потому что она отвечает критерию: ее порядковый номер меньше или равен 5. Если да, Oracle не возвращает строку.
  4. Oracle извлекает следующую строку и присваивает ей слудущий порядковый номер по возрастанию (2, затем 3, затем 4 и т.д.).
  5. Переходим к шгу 3.

Как видно из данного процесса, присвоение значений, возвращаемых функцией ROWNUM, происходит после извлечения очередной строки. Это очень важно и является ключевым моментом. Многие разработчики на Oracle пытаются реализовать извлечение только, скажем, пятой возвращенной запросом строки, задавая ROWNUM = 5. Такое использование условия равенства в сочетании с ROWNUM является неверным. При попытке возвратить пятую строку с помощью ROWNUM = 5 роисходит следующее:

  1. Oracle выполняет запрос.
  2. Oracle извлекает первую строку и называет ее строкой номер 1.
  3. Номер строки равен 5? Если нет, Oracle отбрасывает строку, потому что она не отвечает заданному критерию. Если да, Oracle возвращает строку. Но ответ всегда будет отрицательным!
  4. Oracle извлекает следующую строку и называет ее строкой номер 1, поскольку первая возвращенная запросом строка должна быть пронумерована как первая строка.
  5. Переходим к шагу 3.

После тщательного разбора этого процесса становится понятно, почему использование ROWNUM = 5 не обеспечивает возвращения пятой строки. Невозможно получить пятую строку, не возвратив перед этим строки с первой по четвертую!

Однако заметьте, что с помощью ROWNUM = 1 можно получить первую строку. Может показаться, что это противоречит приведенному выше объяснению. Причина, почему ROWNUM = 1 обеспечивает возвращени первой строки, в том, что Oracle для определения наличия строк в таблице приходится извлекать, по крайней мере, одну из них. Внимательно проанализируйте предыдущий процесс, подставив 1 вместо 5, и вы поймете, почему для возвращения одной строки можно в качестве условия задавать ROWNUM = 1.

Tags: Ограничение числа возвращаемых строк

PL/SQL

tags: Администрирование Oracle DataBase || SQL & PL/SQL

Исходные коды проекта хранятся на github. Можете заводить Issue и Discussions, при необходимости.
Чтобы задать вопрос, добавить свои знания, исправить ошибки и неточности, пишите в телеграм чате.

Команда LIMIT

Команда LIMIT задает ограничение на количество записей, выбираемых из базы данных. Данная команда может использоваться совместно с командой SELECT , командой DELETE , и командой UPDATE .

Синтаксис

SELECT * FROM имя_таблицы WHERE условие LIMIT количество_записей
SELECT * FROM имя_таблицы WHERE условие LIMIT с_какой_записи, количество_записей
SELECT * FROM имя_таблицы WHERE условие ORDER BY по_чем_сортировать LIMIT число, число
DELETE FROM имя_таблицы WHERE условие LIMIT количество_записей
UPDATE имя_таблицы . WHERE условие LIMIT количество_записей

Таблицы для примеров

таблица employees

id
айди
name
имя
age
возраст
salary
зарплата
1 user1 23 400
2 user2 25 500
3 user3 23 500
4 user4 30 900
5 user5 27 500
6 user6 28 900

Пример

Давайте выберем первые 3 записи из таблицы:

SELECT * FROM employees WHERE id>0 LIMIT 3

Результат выполнения кода:

id
айди
name
имя
age
возраст
salary
зарплата
1 user1 23 400
2 user2 25 500
3 user3 23 500

Пример

Давайте выберем 3 записи, начиная со второй (первая имеет номер 0 , а вторая - номер 1):

SELECT * FROM employees WHERE id>0 LIMIT 1, 3

Результат выполнения кода:

id
айди
name
имя
age
возраст
salary
зарплата
2 user2 25 500
3 user3 23 500
4 user4 30 900

Пример

Давайте выберем последние 3 записи из таблицы. Для этого отсортируем их с помощью ORDER BY по убыванию id и возьмем 3 записи с помощью LIMIT.

Они и будут искомыми последними записями, так как LIMIT сработает после сортировки и будет браться по уже отсортированной таблице.

При этом записи будут идти в обратном порядке - с 6 -той по 4 -тую (так как мы их отсортировали):

SELECT * FROM employees WHERE id>0 ORDER BY id LIMIT 3

Результат выполнения кода:

id
айди
name
имя
age
возраст
salary
зарплата
6 user6 28 900
5 user5 27 500
4 user4 30 900

Пример

Давайте выберем последнюю запись в таблице.

Для этого отсортируем их с помощью ORDER BY по убыванию id и возьмем первую запись с помощью LIMIT:

SELECT * FROM employees WHERE id>0 ORDER BY id LIMIT 3

Результат выполнения кода:

id
айди
name
имя
age
возраст
salary
зарплата
6 user6 28 900

Смотрите также

  • команду order ,
    с помощью которой можно отсортировать выбранные записи

Ограничение количества строк LIMIT

Конструкция LIMIT позволяет получить только часть строк от результата запроса.

Применяя LIMIT , важно использовать также предложение ORDER BY , чтобы строки результата выдавались в определённом порядке. Иначе будут возвращаться непредсказуемые подмножества строк. Вы можете запросить строки с десятой по двадцатую, но какой порядок вы имеете в виду? Порядок будет неизвестен, если не добавить ORDER BY .

Например, получим 3 первых магазина в алфавитном порядке:

SELECT store_id, name FROM store ORDER BY name LIMIT 3 
# store_id name
1 900 Big
2 600 Umi
3 300 Адалин

Урок 5. Ограничение и смещение

Кроме фильтров и сортировок, в SELECT-запросах можно также ограничивать выборку. Например, когда вам нужные не все данные, а скажем только первые 10 строк. Для этого существует конструкция LIMIT.

Работать в этом уроке будем с такой таблицей:

Таблица products

id name count price country
1 Телевизор 3 43200.00 RU
2 Микроволновая печь 4 3200.00 UA
3 Холодильник 3 12000.00 RU
4 Роутер 1 1340.00 US
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL
7 Чайник 8 1200.00 RU
8 Дрон 1 45990.00 CH
9 Вентилятор 3 3000.00 RU

Давайте попробуем вывести 5 самых дорогих товаров. Сперва напишем базовый SQL-запрос:

SELECT * FROM products ORDER BY price DESC

И с помощью данного запроса мы получим все товары, отсортированные по цене в обратном порядке — ORDER BY price DESC. То есть дорогие товары будут сверху:

Таблица products с сортировкой

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU
2 Микроволновая печь 4 3200.00 UA
9 Вентилятор 3 3000.00 RU
4 Роутер 1 1340.00 US
7 Чайник 8 1200.00 RU

Теперь, если я оставлю верхние 5 записей, то как раз и получу 5 самых дорогих товаров:

SELECT * FROM products ORDER BY price DESC LIMIT 5

И LIMIT 5 как раз и говорит базе, что нам нужны только 5 первых (верхних) записей. После выполнения запроса мы получим такую таблицу:

5 самых дорогих товара

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU

Теперь давайте получим 5 самых дорогих товаров, которые есть на складе. Для этого нужно написать такой SQL-запрос:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5

После его выполнения выдача немного изменится, так как товары которые закончились в итоговую таблицу не попадают:

5 самых дорогих товара, которые есть на складе

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU
2 Микроволновая печь 4 3200.00 UA

Обратите внимание на порядок следования блоков запроса:

1. Сперва идет SELECT FROM — выбрать ИЗ;
2. Затем блок условия WHERE;
3. После сортировка ORDER BY;
4. И завершает ограничение LIMIT.

Первый блок SELECT FROM обязательный, так как он говорит, что мы хотим сделать.

Остальные блоки опциональны. То есть мы можем убрать любой из них, главное, чтобы сохранялся порядок: (1) WHERE (2) ORDER BY (3) LIMIT.

Смещение с помощью OFFSET

Но вернемся снова к LIMIT. И помимо ограничения выборки, мы также можем делать смещение. Например, искать не первые 5 самых дорогих товаров, а следующую пятерку.

Для этого после LIMIT нужно добавить блок OFFSET:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5 OFFSET 5

И собственно конструкция OFFSET указывает на то, сколько записей нужно пропустить. После запуска мы получим 3 товара, так как изначально в таблице у нас 8 товаров, из которых 5 мы пропускаем с помощью OFFSET, а затем с помощью LIMIT выводим еще 5, но так как после пропуска остается всего 3 товара, то их мы и видим:

Таблица products после смещения и сортировки

id name count price country
6 Холодильник 2 2 14390.00 BL
3 Холодильник 3 12000.00 RU
2 Микроволновая печь 4 3200.00 UA

Постраничный просмотр

Благодаря этой особенности, с помощью LIMIT и OFFSET можно организовывать постраничный просмотр информации. Например, нам нужно выводить по два товара на странице, скажем на каком сайте или в программе.

Тогда в LIMIT мы прописываем двойку, а для OFFSET изначально ставим 0:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 0

После запуска SQL-запроса мы увидим два самых дорогих товара, которые будут отображаться на первой странице:

Товары для первой страницы

id name count price country
8 Дрон 1 45990.00 CH
1 Телевизор 3 43200.00 RU

Далее нам нужно получить следующую пару. Для этого делаем пропуск первых двух товаров:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 2
Товары для второй страницы

id name count price country
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL

Теперь получим товары на третьей странице и для этого мы можем воспользоваться вот такой формулой:
(P — 1) x N, где P — это номер страницы, которая нам нужна, а N – это количество товаров, которые мы выводим на странице.

Значение N у нас фиксировано и указывается в блоке LIMIT. То есть сейчас это 2.
Значение P мы выбираем из набора целых чисел: 1, 2, 3 и тд.
Результат вычисления мы подставляем в блок OFFSET.

Сейчас мы делаем расчеты для третьей страницы, поэтому получаем:
(P — 1) x N = (3 — 1) x 2 = 4

Ставим четверку в OFFSET и получаем финальный SQL-запрос:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 2 OFFSET 4

Который вернет следующую таблицу:

Товары для третьей страницы:

id name count price country
5 Компьютер 0 26150.00 RU
6 Холодильник 2 2 14390.00 BL

С основой мы закончили, но хочу отметить одну особенность конструкции OFFSET, а именно то, что OFFSET в MySQL является частью LIMIT.

То есть мы можем использовать или чистый LIMIT, или LIMIT в паре с OFFSET, но не можем использовать OFFSET сам по себе, так как это будет синтаксической ошибкой.

Также мы не можем менять LIMIT и OFFSET местами.

Что ж, на этом с базовыми возможностями ограничений и смещений мы заканчиваем, а в следующем уроке рассмотрим особенности применения LIMIT и OFFSET в PostgreSQL, MS SQLServer, Oracle и SQLite.

Следующий урок

Урок 6. TOP, LIMIT, FETCH и OFFSET в других базах

Изучаем особенности использования TOP, LIMIT, FETCH и OFFSET в PostgreSQL, MS SQLServer, Oracle и SQLite.

Курс по SQL с бесплатным тест-драйвом

Онлайн-обучение с практикой и поддержкой преподавателя
56 уроков, 260 бизнес-заданий

Попробовать бесплатно

Полный курс с практикой

  • 57 уроков
  • 261 задание
  • Сертификат
  • Поддержка преподавателя
  • Доступ к курсу навсегда
  • Можно в рассрочку

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *