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

Ответ 1

В 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;

Ответ 2

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

Ответ 3

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

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

Другие объяснили, как функция ранжирования 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).

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

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

Ответ 4

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

Ответ 5

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

Проверьте их.

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

Ответ 6

Решение 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

Ответ 7

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

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

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

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

Найдите вторую страницу пользователей, отсортированную по имени, фамилии, где каждая страница имеет 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

Ответ 8

Существует обсуждение этого Здесь

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

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

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