Запрос SQL Server с разбивкой по страницам и подсчету

Ответ 1

Предполагая, что вы используете MSSQL 2012, вы можете использовать Offset and Fetch, который сильно очищает страничный пейджинг. Мы обнаружили, что производительность прекрасна, и в большинстве случаев лучше. Что касается получения общего количества столбцов, просто используйте функцию окна ниже встроенного... он не будет включать ограничения, налагаемые "смещением" и "выборкой".

Для Row_Number вы можете использовать функции окна так, как вы это делали, но я бы рекомендовал вам рассчитать эту клиентскую сторону как (pagenumber * pagesize + resultsetRowNumber), поэтому, если вы находитесь на пятой странице из 10 результатов и на в третьей строке вы выведете строку 53.

При применении к таблице Orders с примерно 2 миллионами заказов я обнаружил следующее:

БЫСТРАЯ ВЕРСИЯ

Это заняло менее секунды. Самое приятное в том, что вы можете сделать свою фильтрацию в обычном табличном выражении один раз, и это применимо как к процессу подкачки, так и к счету. Когда у вас много предикатов в предложении where, это упрощает вещи.

declare @skipRows int = 25,
        @takeRows int = 100,
        @count int = 0

;WITH Orders_cte AS (
    SELECT OrderID
    FROM dbo.Orders
)

SELECT 
    OrderID,
    tCountOrders.CountOrders AS TotalRows
FROM Orders_cte
    CROSS JOIN (SELECT Count(*) AS CountOrders FROM Orders_cte) AS tCountOrders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

МЕДЛЕННАЯ ВЕРСИЯ

Это заняло около 10 секунд, и именно граф (*) вызвал медленность. Я удивлен, что это так медленно, но я подозреваю, что он просто вычисляет общее количество для каждой строки. Это очень чисто.

declare @skipRows int = 25,
@takeRows int = 100,
@count int = 0


SELECT 
    OrderID,
    Count(*) Over() AS TotalRows
FROM Location.Orders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

Заключение

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

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

Ответ 2

DECLARE @pageNumber INT = 1  , 
        @RowsPerPage INT = 20

SELECT  *
FROM    TableName
ORDER BY Id
        OFFSET ( ( @pageNumber - 1 ) * @RowsPerPage ) ROWS
             FETCH NEXT @RowsPerPage ROWS ONLY;

Ответ 3

Что делать, если вы заранее подсчитали счет?

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;

SELECT @total = count(*)
FROM @table

with query as
(
   select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name, @total total from query
where line > (@pagenumber - 1) * @pagesize

Другой способ - рассчитать max(line). Проверьте ссылку

Возвращает итоговые записи из SQL Server при использовании ROW_NUMBER

UPD:

Для одного запроса проверьте ответ marc_s по ссылке выше.

    with query as
    (
       select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
    )
    select top (@pagesize) name, 
       (SELECT MAX(line) FROM query) AS total 
    from query
    where line > (@pagenumber - 1) * @pagesize

Ответ 4

@pagenumber=5
@pagesize=5

Создайте общее табличное выражение и напишите логику следующим образом

Between ((@pagenumber-1)*(@pagesize))+1 and (@pagenumber *@pagesize)

Ответ 5

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

Пример 1: использование следующего предложения offset-fetch. представить в 2005 году

declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');

declare @pagenumber int = 1
declare @pagesize int = 3

--this is a CTE( common table expression and this is introduce in 2005)
with query as
(
  select ROW_NUMBER() OVER(ORDER BY name ASC) as line, name from @table
) 

--order by clause is required to use offset-fetch
select * from query
order by name 
offset ((@pagenumber - 1) * @pagesize) rows
fetch next @pagesize rows only

Пример 2: использование функции row_number() и между

declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');

declare @pagenumber int = 2
declare @pagesize int = 3

SELECT *
FROM 
(select ROW_NUMBER() OVER (ORDER BY PRODUCTNAME) AS RowNum, * from Products)
as Prodcut
where RowNum between (((@pagenumber - 1) * @pageSize )+ 1) 
and (@pagenumber * @pageSize )

Я надеюсь, что это будет полезно для всех