Microsoft SQL Server
пагинация
Поиск…
Вступление
Синтаксис
- SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Разбиение на страницы с использованием ROW_NUMBER с общим выражением таблицы
Функция ROW_NUMBER
может назначать инкрементный номер каждой строке в результирующем наборе. В сочетании с общим выражением таблицы , использующим оператор BETWEEN
, можно создавать «страницы» наборов результатов. Например: страница 1, содержащая результаты 1-10, вторая страница содержит результаты 11-20, стр. 3, содержащие результаты 21-30 и т. Д.
WITH data
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY name) AS row_id,
object_id,
name,
type,
create_date
FROM sys.objects
)
SELECT *
FROM data
WHERE row_id BETWEEN 41 AND 50
Примечание. Невозможно использовать ROW_NUMBER
в предложении WHERE
, например:
SELECT object_id,
name,
type,
create_date
FROM sys.objects
WHERE ROW_NUMBER() OVER (ORDER BY name) BETWEEN 41 AND 50
Хотя это было бы более удобно, SQL Server вернет следующую ошибку в этом случае:
Msg 4108, уровень 15, состояние 1, строка 6
Оконные функции могут отображаться только в предложениях SELECT или ORDER BY.
Разбиение страницы с помощью OFFSET FETCH
Предложение OFFSET FETCH
реализует разбиение на страницы более кратким образом. С его помощью можно пропустить строки N1 (указанные в OFFSET
) и вернуть следующие строки N2 (указанные в FETCH
):
SELECT *
FROM sys.objects
ORDER BY object_id
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY
Предложение ORDER BY
требуется для обеспечения детерминированных результатов.
Paginaton с внутренним запросом
В более ранних версиях SQL Server разработчикам приходилось использовать двойную сортировку в сочетании с ключевым словом TOP
чтобы возвращать строки на странице:
SELECT TOP 10 *
FROM
(
SELECT
TOP 50 object_id,
name,
type,
create_date
FROM sys.objects
ORDER BY name ASC
) AS data
ORDER BY name DESC
Внутренний запрос вернет первые 50 строк, упорядоченных по name
. Затем внешний запрос изменит порядок этих 50 строк и выберет первые 10 строк (это будут последние 10 строк в группе до разворота).
Пейджинг в различных версиях SQL Server
SQL Server 2012/2014
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM OrderDetail
ORDER BY OrderId
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
SQL Server 2005/2008 / R2
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM (
SELECT OrderId, ProductId, ROW_NUMBER() OVER (ORDER BY OrderId) AS RowNum
FROM OrderDetail) AS OD
WHERE OD.RowNum BETWEEN ((@PageNumber - 1 ) * @RowsPerPage) + 1
AND @RowsPerPage * @PageNumber
SQL Server 2000
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM (SELECT TOP (@RowsPerPage) OrderId, ProductId
FROM (SELECT TOP ((@PageNumber)*@RowsPerPage) OrderId, ProductId
FROM OrderDetail
ORDER BY OrderId) AS OD
ORDER BY OrderId DESC) AS OD2
ORDER BY OrderId ASC
SQL Server 2012/2014 с использованием ORDER BY OFFSET и FETCH NEXT
Для получения следующих 10 строк просто запустите этот запрос:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Ключевые моменты, которые следует учитывать при его использовании:
-
ORDER BY
является обязательным для использования предложенийOFFSET
иFETCH
. - Предложение
OFFSET
является обязательным дляFETCH
. Вы никогда не сможете использоватьORDER BY
...FETCH
. -
TOP
нельзя комбинировать сOFFSET
иFETCH
в одном выражении запроса.