Как вернуть страницу результатов из SQL?

На MS SQL Server 2005 и выше ROW_NUMBER (), похоже, работает:

T-SQL: пейджинг с помощью ROW_NUMBER ()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

Фактически, LINQ имеет методы Skip и Take, которые можно комбинировать, чтобы выбрать, какие записи будут извлечены.

Проверьте это.

Для БД: Разбиение на страницы в SQL Server 2005

Решение Oracle:

select * from (
    select a.*, rownum rnum from (
        YOUR_QUERY_GOES_HERE -- including the order by
    ) a
    where rownum <= MAX_ROW
 ) where rnum >= MIN_ROW

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

MyDataContext db = new MyDataContext();
var results = db.Products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

Запуск SQL Server Profiler показывает, что LINQ преобразует этот запрос в SQL аналогично:

SELECT [ProductId], [Name], [Cost], and so on...
FROM (
    SELECT [ProductId], [Name], [Cost], [ROW_NUMBER]
    FROM (
       SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [ROW_NUMBER], 
           [ProductId], [Name], [Cost]
       FROM [Products]
    )
    WHERE [ROW_NUMBER] BETWEEN 10 AND 20
)
ORDER BY [ROW_NUMBER]

На простом английском языке:
1. Отфильтруйте свои строки и используйте функцию ROW_NUMBER для добавления номеров строк в нужном вам порядке. 2. Отфильтруйте (1), чтобы получить на странице только те номера строк, которые вам нужны. 3. Отсортируйте (2) по номеру строки, который соответствует желаемому порядку (в данном случае по имени).

Есть несколько решений, которые я использую с MS SQL 2005.

Один из них - ROW_NUMBER (). Но лично мне не нравится ROW_NUMBER (), потому что он не работает для больших результатов (БД, над которой я работаю, действительно большая - более 1 ТБ данных, выполняющих тысячи запросов в секунду - ну, вы знаете - большие социальные сети сайт).

Вот мое любимое решение.

Я буду использовать своего рода псевдокод T-SQL.

Найдем 2-ю страницу пользователей, отсортированную по имени, фамилии, где на каждой странице по 10 записей.

@page = 2 -- input parameter
@size = 10 -- can be optional input parameter

if @page < 1 then begin
    @page = 1 -- check page number
end
@start = (@page-1) * @size + 1 -- @page starts at record no @start

-- find the beginning of page @page
SELECT TOP (@start)
    @forename = forename,
    @surname = surname
    @id = id
FROM
    users
ORDER BY
    forename,
    surname,
    id -- to keep correct order in case of have two John Smith.

-- select @size records starting from @start
SELECT TOP (@size)
    id,
    forename,
    surname
FROM
    users
WHERE
    (forename = @forename and surname = @surname and id >= @id) -- the same name and surname, but bigger id
    OR (forename = @forename and surname > @surname) -- the same name, but bigger surname, id doesn't matter
    OR (forename > @forename) -- bigger forename, the rest doesn't matter
ORDER BY
    forename,
    surname,
    id

LINQ в сочетании с лямбда-выражениями и анонимными классами в .Net 3.5 чрезвычайно упрощает подобные вещи.

Запрос базы данных:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select c;

Количество записей на странице:

customers = customers.Skip(pageNum * pageSize).Take(pageSize);

Сортировка по любому столбцу:

customers = customers.OrderBy(c => c.LastName);

Получение с сервера только выбранных полей:

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select new
                {
                    CustomerID = c.CustomerID,
                    FirstName = c.FirstName,
                    LastName = c.LastName
                };

Это создает статически типизированный анонимный класс, в котором вы можете получить доступ к его свойствам:

var firstCustomer = customer.First();
int id = firstCustomer.CustomerID;

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

Есть обсуждение этого Здесь

Метод получает номер страницы 100 000 из базы данных 150 000 строк за 78 мс.

Using optimizer knowledge and SET ROWCOUNT, the first EmployeeID in the page that is requested is stored in a local variable for a starting point. Next, SET ROWCOUNT to the maximum number of records that is requested in @maximumRows. This allows paging the result set in a much more efficient manner. Using this method also takes advantage of pre-existing indexes on the table as it goes directly to the base table and not to a locally created table.

Боюсь, я не могу судить, лучше ли он, чем принятый в настоящее время ответ.

По сути, есть два способа разбивки на страницы в базе данных (я предполагаю, что вы используете SQL Server):

Использование смещения

Другие объяснили, как функцию ранжирования ROW_NUMBER() OVER() можно использовать для выполнения страниц. Стоит отметить, что SQL Server 2012 наконец-то включил поддержку стандартного предложения SQL OFFSET .. FETCH:

SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

Если вы используете SQL Server 2012 и обратная совместимость не является проблемой, вам, вероятно, следует предпочесть это предложение, поскольку оно будет выполняться SQL Server более оптимально в крайних случаях.

Использование метода SEEK

Существует совершенно другой, гораздо более быстрый, но менее известный способ выполнения подкачки в SQL. Это часто называют «методом поиска», как описано в это сообщение в блоге здесь.

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

Значения @previousScore и @previousPlayerId являются соответствующими значениями последней записи с предыдущей страницы. Это позволяет вам перейти на «следующую» страницу. Если направление ORDER BY - ASC, просто используйте вместо него >.

С помощью описанного выше метода вы не можете сразу перейти на страницу 4, не загрузив сначала предыдущие 40 записей. Но зачастую вы все равно не хотите прыгать так далеко. Вместо этого вы получаете гораздо более быстрый запрос, который может извлекать данные за постоянное время, в зависимости от вашей индексации. Кроме того, ваши страницы остаются «стабильными» независимо от того, изменяются ли базовые данные (например, на странице 1, пока вы находитесь на странице 4).

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

Обратите внимание, что «метод поиска» также называется пейджинг с клавиатуры.