Начиная с Microsoft SQL Server 2012, стало возможно использовать конструкцию OFFSET-FETCH для реализации постраничной выборки, сейчас мы подробно рассмотрим данную конструкцию и разберем примеры ее использования.
До выхода SQL Server 2012 реализовать постраничную выборку можно было, например, с помощью ранжирующих функций, пример такого подхода можете посмотреть в материале – «Постраничная выборка на T-SQL – пример реализации».
В SQL Server 2012 возможности инструкции ORDER BY были расширены, а именно: добавилась конструкция OFFSET-FETCH, которая как раз и позволяет оставлять в результирующем наборе только ту часть данных и то количество строк, которое нам необходимо.
Описание OFFSET-FETCH
OFFSET-FETCH – это конструкция языка Transact-SQL, которая является частью ORDER BY, и позволяет применять фильтр к результирующему, уже отсортированному, набору данных.
OFFSET-FETCH предназначена как раз для разбиения результирующего набора на части (страницы), а также для ограничения количества строк.
В языке T-SQL есть оператор TOP, с помощью которого мы можем примерно так же ограничивать количество строк, возвращаемых запросом. Однако TOP не позволяет пропускать строки, т.е. мы всегда получаем строки, начиная с первой, учитывая сортировку, т.е. с помощью сортировки мы можем влиять на результат.
Используя конструкцию OFFSET-FETCH, мы уже можем пропускать определённое количество строк.
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL.
Упрощённый синтаксис OFFSET-FETCH
ORDER BY Выражение для сортировки
OFFSET Целое число ROWS FETCH NEXT Целое число ROWS ONLY
Где,
- ORDER BY – инструкция для сортировки данных, возвращаемых запросом;
- OFFSET Целое число ROWS – инструкция задает количество строк, которые необходимо пропустить. Вместо ROWS можно использовать ключевое слово ROW, они эквиваленты. Однако ROWS и ROW могут сделать код более читабельным, например, ROWS использовать для пропуска нескольких строк, а ROW — для пропуска одной строки (т.е. единственное и множественное число, но снова повторюсь, они взаимозаменяемы);
- FETCH NEXT Целое число ROWS ONLY – инструкция задает количество строк, которые необходимо вернуть, после обработки инструкции OFFSET. Вместо ROWS можно использовать ключевое слово ROW, они эквиваленты. Также вместо NEXT можно использовать ключевое слово FIRST.
Важные замечания по использованию OFFSET-FETCH
- OFFSET-FETCH – это часть ORDER BY, без сортировки использовать конструкцию OFFSET-FETCH не получится;
- Инструкцию OFFSET можно использовать без указания FETCH, а вот FETCH использовать без указания OFFSET нельзя, т.е. FETCH требует обязательного наличия OFFSET;
- Не поддерживается совместная работа операторов TOP и OFFSET-FETCH в одном запросе SELECT.
Примеры использования OFFSET-FETCH в T-SQL
Сейчас давайте рассмотрим несколько примеров использования конструкции OFFSET-FETCH в языке T-SQL, но сначала давайте определимся с исходными данными.
Исходные данные для примеров
Допустим, у нас есть таблица TestTable, и она содержит следующие данные. В качестве сервера у меня выступает Microsoft SQL Server 2016 Express.
--Создание таблицы CREATE TABLE TestTable( [ProductId] [INT] IDENTITY(1,1) NOT NULL, [ProductName] [VARCHAR](100) NOT NULL, [Price] [Money] NULL ) GO --Добавление строк в таблицу INSERT INTO TestTable(ProductName, Price) VALUES ('Системный блок', 300), ('Монитор', 200), ('Клавиатура', 100), ('Мышь', 50), ('Принтер', 200), ('Сканер', 150), ('Телефон', 250), ('Планшет', 300) GO --Выборка данных SELECT * FROM TestTable
Заметка! Обзор инструментов для работы с Microsoft SQL Server.
OFFSET-FETCH – пропуск первых 3 строк
В этом примере мы пропустим первые три строки результирующего набора и вернем все последующие строки. Для этого мы просто напишем OFFSET 3 ROWS после определения инструкции ORDER BY.
--Пропуск первых 3 строк SELECT * FROM TestTable ORDER BY ProductId OFFSET 3 ROWS
OFFSET-FETCH – пропуск первых 3 строк и возвращение следующих 3
В данном случае мы также пропустим первые три строки, только дополнительно мы еще укажем инструкцию FETCH NEXT 3 ROWS ONLY, которая будет говорить SQL серверу о том, что нужно вернуть не все последующие строки, а только 3 следующие.
Как Вы понимаете, значение 3 в обоих случаях можно изменять на то значение, которое нужно Вам, также вместо константы (т.е. цифры 3) можно подставлять и переменные, и выражения, которые возвращают целое значение.
--Пропуск первых 3 строк и возвращение следующих 3 SELECT * FROM TestTable ORDER BY ProductId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY
Заметка! Все возможности языка SQL и T-SQL очень подробно рассматриваются на моих курсах по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать на T-SQL в Microsoft SQL Server.
Вот мы с Вами и рассмотрели конструкцию OFFSET-FETCH языка T-SQL, надеюсь, всё было понятно, удачи!
Приглашаю всех желающих пройти мои онлайн-курсы по изучению языка T-SQL – https://self-learning.ru/courses/t-sql
На курсах используется моя авторская последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL. Каждый курс включает огромное количество материалов: видео, текстовый материал, тесты, домашние задания, скрипты, а также сертификат о прохождении.
На курсах Вы можете заниматься в комфортном для себя темпе не выходя из дома в любое удобное для Вас время.