Постраничная выборка на T-SQL – пример реализации

Сегодня в материале будет рассмотрен простой пример реализации постраничной выборки на языке T-SQL с использованием ранжирующих функций, в процессе этого мы вспомним, что такое ROW_NUMBER() и DENSE_RANK(), как писать табличные функции, как использовать CTE и многое другое.

Итак, если у Вас возникла необходимость реализовать возможность запрашивать определенное количество строк источника (таблицы, представления и другие) с учетом их последовательности, т.е. например, у Вас есть 100 товаров и Вам нужно сначала получить с 1 по 10, затем с 11 по 20, с 21 по 30 и так далее. Яркий пример такой возможности реализуется практически на каждом сайте в интернете, когда материалов или товаров в категории много и на одной странице отображается только небольшая их часть, например, по 10 материалов на странице. А внизу или вверху страницы есть элемент навигации, который позволяет перейти на следующую, предыдущую или на любую другую страницу, например 10, 20…100 и так далее. Это и называется «Постраничная навигация» и сейчас я покажу, как можно все это реализовать на T-SQL в MS SQL Server.

Примечание! В качестве примера у меня будет Microsoft SQL Server 2012 Express.

Примечание! На некоторых моментах в материале я не буду заострять внимания, и соответственно не буду подробно пояснять их (например, создание таблицы на T-SQL и так далее), так как материал посвящен не этому, поэтому подразумевается, что Вы это должны уже знать, если нет, то рекомендую сначала посмотреть «Справочник языка T-SQL», а также прочитать материал «Основы программирования на T-SQL».

Давайте приступать, сначала рассмотрим исходные данные.

Исходные данные

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

 
   CREATE TABLE TestTable(
        Id int IDENTITY(1,1) NOT NULL, --Идентификатор товара
        ProductName VARCHAR(50) NOT NULL, --Наименование товара
        Price MONEY NULL, --Цена товара
        IdGroup INT NULL,--Группа товара
        CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (Id ASC)
   )

Заполним ее тестовыми данными.

 
   --Переменные
   DECLARE @RowNumber AS INT = 1
   DECLARE @ProductName AS VARCHAR(20), @Price AS MONEY, @IdGroup AS INT 
   --Начальное значение цены
   SET @Price = 100
   --Запускаем цикл на 100 итераций, т.е. вставим 100 строк
   WHILE @RowNumber <= 100
   BEGIN
        --Изменяем наименование товара
        SET @ProductName = 'Товар ' + CAST(@RowNumber AS VARCHAR(20))
        --Изменяем цену товара
        SET @Price = @Price + @RowNumber 
        --Присваиваем случайную группу
        SET @IdGroup = ROUND(Rand() * 10, 0)
        --Вставляем данные
        INSERT INTO TestTable(ProductName, Price, IdGroup)
                                  VALUES (@ProductName, @Price, @IdGroup)
        --Переходим к следующей итерации
        SET @RowNumber = @RowNumber + 1
   END

Проверим, что в итоге у нас находится в таблице TestTable.

 
   SELECT * FROM TestTable

Создаем табличную функцию для постраничной навигации на T-SQL

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

 
   CREATE FUNCTION dbo.ft_GetPage(@Page INT, --Номер страницы
                                  @CntRowOnPage AS INT --Количество записей на странице
                                  ) 
   RETURNS TABLE
   RETURN(
   --Объявляем CTE
   WITH SOURCE AS (
        SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber, * 
        FROM TestTable
   )
   SELECT * FROM SOURCE
   WHERE RowNumber > (@Page * @CntRowOnPage) - @CntRowOnPage 
     AND RowNumber <= @Page * @CntRowOnPage
  )

Здесь мы использовали обобщенное табличное выражение (CTE) для удобства, можно также было использовать и подзапрос, но с CTE выглядит наглядней. Ранжирующей функцией ROW_NUMBER() мы пронумеровали строки с сортировкой по Id. Затем с помощью простых действий мы фильтруем полученные данные на основе входящих параметров: @Page – для определения страницы или части наших данных и @CntRowOnPage – для определения количества записей на странице или в определенной части данных.

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

 
   SELECT * FROM dbo.ft_GetPage (2, 10)

Мы видим, что функция работает (в моем примере она вернула вторую страницу с 10 записями).

Заметка! Как сформировать и вставить в таблицу заданное количество строк на T-SQL.

Табличная функция для постраничной навигации групп на T-SQL

Если помните, у нас в таблице есть идентификатор группы и теперь представим, что у нас встала необходимость реализовать постраничную навигацию групп, т.е. нам нужно выводить на первой странице содержимое, к примеру, 3 групп, на второй содержимое следующих 3 групп и так далее.

Для этого мы напишем новую табличную функцию, немного модифицировав ее, а именно вместо ранжирующей функции ROW_NUMBER() будем использовать другую ранжирующую функцию DENSE_RANK(), которая может пронумеровать группы (в качестве сортировки здесь мы уже указываем идентификатор группы).

 
   CREATE FUNCTION dbo.ft_GetPageGroup(@Page INT, --Номер страницы
                                       @CntRowOnPage AS INT -- Количество групп на странице
                                       ) 
   RETURNS TABLE
   RETURN(
   --Объявляем CTE
   WITH SOURCE AS (
        SELECT DENSE_RANK() OVER (ORDER BY IdGroup) AS RowNumber, * 
        FROM TestTable
   )
   SELECT * FROM SOURCE
   WHERE RowNumber > (@Page * @CntRowOnPage) - @CntRowOnPage 
     AND RowNumber <= @Page * @CntRowOnPage
   )

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

 
   SELECT * FROM dbo.ft_GetPageGroup (1, 3)

Для получения всех записей вторых 3 групп в первом параметре указываем 2 и так далее.

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, которые помогут Вам «с нуля» научиться работать с SQL и программировать на T-SQL в Microsoft SQL Server.

На этом у меня все, надеюсь, материал был Вам полезен. Удачи!