OFFSET-FETCH в T-SQL – описание и примеры использования

Начиная с Microsoft SQL Server 2012, стало возможно использовать конструкцию OFFSET-FETCH для реализации постраничной выборки, сейчас мы подробно рассмотрим данную конструкцию и разберем примеры ее использования.

OFFSET-FETCH в T-SQL

До выхода 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, мы уже можем пропускать определённое количество строк.

Всем тем, кто только начинает свое знакомство с языком T-SQL, рекомендую прочитать книгу «Путь программиста T-SQL. Самоучитель по языку Transact-SQL», которую написал лично я, и в которой я подробно, и в то же время простым языком, рассказываю о языке T-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

Скриншот 2

OFFSET-FETCH – пропуск первых 3 строк

В этом примере мы пропустим первые три строки результирующего набора и вернем все последующие строки. Для этого мы просто напишем OFFSET 3 ROWS после определения инструкции ORDER BY.

   
   --Пропуск первых 3 строк
   SELECT * FROM TestTable
   ORDER BY ProductId
   OFFSET 3 ROWS

Скриншот 3

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

Скриншот 4

Вот мы с Вами и рассмотрели конструкцию OFFSET-FETCH языка T-SQL, надеюсь, всё было понятно, удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.