Microsoft SQL Server
Transact-SQL

Постраничная выборка

Опубликовано: 27 сен 02
Рейтинг:

Автор: Glory
Прислал:

Вопрос порционной(постраничной) выборки тесно связан с вопросом нумерации строк в запросе (см. FAQ.Возвращение порядковых номеров строк в результирующем наборе). Другими словами, для того, чтобы выбрать N-ую порцию из результатов запроса, нужно сначала пронумеровать результаты этого запроса.

Отсюда и похожие методы решения

Вариант 1. «Классический».

SELECT TOP 100 * FROM MyTable 
WHERE id NOT IN (SELECT TOP 100 id FROM MyTable ORDER BY id) ORDER BY id

Главный недостаток этого метода в в том, что т.к. TOP n записей выбираются уже из конечного результата запроса, то проверка условия WHERE будет выполняться для каждой строки главного запроса. При этом, время выполнения этой проверки будет расти вместе с номером выбираемой порции(страницы). Если, например, таблица содержит 100 записей и необходимо выбирать данные порциями по 10 записей, то
для 2-ой порции нужно будет будет проверять подзапрос из 10 записей
для 3-ей порции нужно будет будет проверять подзапрос из 20 записей
для 4-ой порции нужно будет будет проверять подзапрос из 30 записей и т.д.

Недостатком также является невозможность задать значение для TOP с помощью переменной. Что ведет к необходимости использования динамического запроса.

Достоинство метода в его универсальности, академичности. Он не требует специфики T-SQL, этот метод можно применить практически на любом SQL-сервере.


Вариант 2 «Эффективный, специфический для T-SQL».

Как и в случае нумерации строк данный метод основан на использовании временной таблицы. Для удобства оформим наш запрос как хранимую процедуру, возвращающую n-ую порцию(страницу), содержащую m записей

CREATE PROCEDURE dbo.get_this_page (@rec_per_page int, @page_num int) AS

SELECT identity(int, 1,1) AS RowNum, MyId AS OrigId INTO #tmp FROM mytable
SELECT b.* FROM #tmp AS a
INNER JOIN mytable AS b on a.OrigId = b.MyId 
WHERE a.RowNum BETWEEN (@rec_per_page * @page_num + 1) 
   AND (@rec_per_page * (@page_num + 1))

DROP TABLE #tmp
Примечания.
- предложенный вариант процедуры будет блокировать некоторые системные таблицы базу tempdb на все время выполнения 1-го запроса. Если время блокировки становиться неприемлимым, то необходимо отделить создание временной таблицы от заполнеения ее данными вот запрос таким образом
CREATE TABLE #temp(RowNum int identity, OrigId int)
INSERT INTO #temp(OrigId) SELECT MyId FROM mytable

- Если, поле MyId было создано признаком «IDENTITY», то это поле в запросе необходимо «завернуть» в функцию «CONVERT», иначе будет сообщение об ошибке.

Вариант 3 (от Cat2). "Постраничная выборка, использующая временные таблицы".
Будет корректно работать только если на полях, по которым производится выборка построены индексы

/*
Создаем тестовую таблицу. По полям i1 и d строим уникальные индексы
*/
set nocount on
create table list (
id int identity(1,1) primary key,
i1 int,
i2 int,
d datetime
)
CREATE  INDEX ind_i ON list(i1)
CREATE  INDEX ind_d ON list(d)
go

set nocount on
declare @i int
declare @m int
set @i=1
while @i<=30
begin
	insert into list (i1) values (null)
	set @i=@i+1
end

/*
Заполняем тестовую таблицу. В поля i1 и i2 заносим числа от 1 до 30, а в d даты с 1 по 30 января 
2000 года
*/

select id into #t from list
while exists (select * from list where i1 is Null)
begin
	set @i=rand()*30+1
	set @m=(select max(id) from #t)
	update list 
	set i1=@m,
	i2=@m,
	d=convert(datetime,'20020101',112)+@m-1
	where id=@i and i1 is null
	if @@rowcount>0
	delete from #t where id = @m
end
drop table #t 

go

--Выбираем "строки" с 11 по 20


/*
Даже если выборка идет по первичному ключу его лучше указать.
В противном случае возможны всякие неожиданности
*/
SELECT identity(int,1,1) AS RowNum, convert(int,id) as id  
into #t FROM list with (index(0))

select t.RowNum as [По id],t.id,l.i1,l.i2,l.d from #t t
join list l on l.id=t.id
where RowNum between 11 and 20
order by RowNum
drop table #t
go

/*
Выборка по полю, по которому нет индекса
Этот код не будет работать так, как надо, несмотря на order by i2
Подробднее об этом написано здесь - http://support.microsoft.com/default.aspx?scid=kb;en-us;273586
*/
SELECT identity(int,1,1) AS RowNum, i2, convert(int,id) as id 
into #t FROM list
order by i2

select t.RowNum  as [По i2],l.id,l.i1,t.i2,l.d 
from #t t
join list l on l.id=t.id
where RowNum between 11 and 20
order by RowNum
drop table #t
go

/*
Выборка по полю, по которому индекс построен
Этот код делает то, что надо
*/
SELECT identity(int,1,1) AS RowNum, i1, convert(int,id) as id i
nto #t FROM list with (index(ind_i))

select t.RowNum  as [По i1],t.id,t.i1,l.i2,l.d 
from #t t
join list l on l.id=t.id
where RowNum between 11 and 20
order by RowNum

drop table #t
go

/*
Выборка по дате в порядке убывания
*/
SELECT identity(int,1,1) AS RowNum, d, convert(int,id) as id 
into #t FROM list with (index(ind_d))
order by d desc

select t.RowNum as [По d],t.id,l.i1,l.i2,t.d
from #t t
join list l on l.id=t.id
where RowNum between 11 and 20

drop table #t
go
drop table list

Вариант 4 (от Глеба Уфимцева). Использование курсора.

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

1. Обращение к новой странице выборки не должно приводить к перезапросу всей выборки.
2. Запрос страницы должен возвращать обычный привычный рекордсет.
3. Инициация не должна быть чрезмерно долгой, чтобы запрос к таблице в 1000000 строк не утомил ожиданием.
4. В любой момент должно быть разрешено выбрать любую страницу из выборки, в том числе и ранее выбранную.
5. Страница должна содержать любое задаваемое кол-во строк.
6. Страница должна начинаться с любой задаваемой строки по номеру.

Решение, удовлетворяющее всем этим пожеланиям стразу, было найдено. Вот оно:

1. Имеем запрос, который мы хотим выбирать постранично

select * from BigTable
мы его не запускаем, а переходим к шагу 2.

2. Инициализируем таким образом:

declare @handle int,@rows int
exec sp_cursoropen @handle OUT, 'select * from BigTable',1, 1, @rows OUT
select @handle, @rows

При этом получаем пустой рекордсет, содержащий метаданные-описания колонок, которые можно использовать для получения названия полей (и типов). После NextRecordset также получаем хендл получившегося курсора и кол-во строк во всей выборке. Хендл нам понадобиться для подстановки в следующие вызовы, и его надо сохранить на клиенте в глобальную переменную, а кол-во строк может быть использовано для определения кол-ва страниц.

3. Получаем нужную страницу из выборки:

exec sp_cursorfetch @handle,16,@rowid,@rowcount

Где в @handle подставляем сохраненное значение хендла, в @rowid подставляется номер строки, с которой начинается интересующая нас страница, а в @rowcount подставляется кол-во строк на данной странице.

Шаг 3 повторяем столько сколько нужно раз.

4. Освобождаем ресурсы после того, как страницы уже больше не понадобятся

exec sp_cursorclose @handle

Комментарии


  • Вот официальный источник http://msdn.microsoft.com/en-us/library/ms188385.aspx#Offset

  • Изменилось MS SQL 2012 имеет встроенную поддержку пейджинга через OFFSET и FETCH. http://t-sql.ru/post/OFFSET-and-FETCH.aspx

  • 2013 вот вот наступит, а у них ничего не изменилось...

  • 10 мая 2011, 13:22 Mikhail Tchervonenko

    начиная с 2005 сервера
    select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
    from authors a
    where
    rank > @RowFrom AND rank <= @RowTo

  • А почему такой вариант никто не предлагает?
    select ....,
    from
    (select ....,
    ROW_NUMBER() OVER(order by AgentID) as RowNum
    from
    dbo.Agents
    where ....
    ) a
    where
    a.RowNum > @RowFrom AND a.RowNum <= @RowTo

  • Описание разных способов, тесты и сравнения.
    http://arbinada.com/main/node/463

  • Второй вариант "Классического". Может показывать лучшую производительность:

    SELECT TOP 100 * FROM (SELECT TOP 200 * FROM MyTable ORDER BY id) t ORDER BY id desc

  • Написано не плохо зачет!
    -----------------------------
    Вот вам еще вариант работает даже при использовании uniqueidentifier как ключевого поля таблицы.

    1. Создаем виртуальную выборку OrderedObjects сортированню по нужной колонке:

    WITH OrderedObjects AS
    ( SELECT ROW_NUMBER() OVER(Order BY ObjectOrder) AS RowNumber,
    ObjectsView.* FROM ObjectsView WHERE TypeID=11)

    2. Выбираем из OrderedObjects где объкеты в пределах 10 до 20, тоесть вторая страница:

    SELECT * FROM OrderedObjects
    WHERE RowNumber between 10 and 20

  • 2maXmo: Ожидания не оправдались. ROWNUMBER() вещь ))

  • 06 марта 2007, 02:01 Robert Tappan Moris

    Как мне кажется, второй вариант можно переписать без испрользования временных таблиц (может быть я и не прав, но не люблю я их):
    CREATE PROCEDURE dbo.get_this_page (@rec_per_page int, @page_num int) AS
    SELECT
    *
    FROM
    mytable
    WHERE
    -- подзапрос: для поиска номера строки на
    -- основе количества строк с ID меньшим чем
    -- ID текущей строки
    (
    SELECT
    COUNT(MyId)
    FROM
    mytable m_cnt
    WHERE
    m_cnt.MyId<=mytable.MyId
    ) BETWEEN
    -- и традиционное ограничение по номерам строк
    (@rec_per_page * @page_num + 1)
    AND (@rec_per_page * (@page_num + 1))
    P.S. писать запросы научился, вот проверить эффективность - нет, так что если запрос не эффективен, просьба ногами не пинать, кстати кто-то, кажется просил без временных таблиц, но я не знаю о каком пункте шла речь =(

  • На msdn статья на эту тему
    http://msdn2.microsoft.com/en-us/library/ms979197.aspx

  • 5 баллов! Второй вариант - то что надо, замечательно подошел, тока пришлось доработать наслабо для сортировки по параметрам - названиям полей %)

  • 4 вариант - приводит к ошибке, если закрыть хэндл

  • всё-то у них через *опу. И у меня почему-то сильные подозрения, что в 2005-м сервере придётся корячиться точно так же.

  • полная ж по сравнению с другими популярными субд. хоть вешайся.

  • Кстати, желательно без временный таблиц

  • Вариант с курсором вне конкуренции

  • 21 января 2005, 15:31 Надо убизапрещать таких ублюдков

    как мы

  • подписался



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Постраничная выборка