Как вернуть страницу результатов из 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, либо попытаться скопировать то, что он делает. У меня есть приложение, в котором я использую методы LINQ Take and Skip для извлечения данных по страницам. Код выглядит примерно так:

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) по номеру строки, который совпадает с требуемым порядком (в данном случае по имени).

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

Использование OFFSET

Другие объяснили, как функция ранжирования ROW_NUMBER() OVER() может использоваться для выполнения страниц. Стоит отметить, что SQL Server 2012 наконец-то включил поддержку предложения SQL standard 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 в угловых случаях.

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

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

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 являются соответствующими значениями последней записи с предыдущей страницы. Это позволяет вам получить страницу "next". Если направление ORDER BY равно ASC, просто используйте вместо него > .

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

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

Обратите внимание, что "seek method" также называется подкачкой набора ключей .

LINQ в сочетании с lambda выражениями и анонимными классами в .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 также значительно упрощает обновление, сохраняя datacontext всех внесенных вами изменений и обновляя только те поля, которые вы изменили.

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

Один из них-ROW_NUMBER(). Но лично мне не нравится ROW_NUMBER(), потому что он не работает для больших результатов (DB, над которым я работаю, действительно большой-более 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 имеет методы Skip и Take, которые можно комбинировать, чтобы выбрать, какие записи будут извлечены.

Посмотри на них.

Для DB: Разбиение На Страницы В 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

Здесь идет дискуссия по этому поводу

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

Используя знания оптимизатора и установив ROWCOUNT, первый EmployeeID на запрашиваемой странице сохраняется в локальной переменной для начальной точки. Затем установите значение ROWCOUNT на максимальное число записей, которое запрашивается в @maximumRows.. это позволяет намного эффективнее выполнять разбиение результирующего набора на страницы. Использование этого метода также использует преимущества уже существующих индексов в таблице, поскольку он идет непосредственно к базовой таблице, а не к локально созданной таблице.

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