Как получить N строк, начиная с строки M из отсортированной таблицы в T-SQL

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

select *
from
(
    select
        Row_Number() over (order by ClusteredIndexField) as RowNumber,
        *
    from MyTable
) as PagedTable
where RowNumber between @LowestRowNumber and @HighestRowNumber;

при получении страницы 1 запрос занимает 0,577 секунды. Однако, при получении страницы 15,619, этот же запрос занимает более 2 минут и 55 секунд.

мы можем значительно улучшить это путем создавать рекордный номер, перекрестную таблицу индекса как показано внутри следующий запрос. Кросс-таблица называется PagedTable и не является постоянной.

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

как и в предыдущем примере, я проверил это на очень широкой таблице с 780 928 записями. Я использовал размер страницы 50, что привело к 15 619 страниц.

общее время, затраченное на страницу 1 (первая страница), составляет 0,413 секунды. Общее время, затраченное на страницу 15 619 (последняя страница), составляет 0,987 секунды, что в два раза больше, чем на странице 1. Эти времена были измерены с помощью SQL Server Профилировщиком и СУБД был SQL Server 2008 R2.

Это решение работает для любого случая, когда вы сортируете таблицу по индексу. Индекс не обязательно должен быть кластеризованным или простым. В моем случае индекс состоял из трех полей: varchar(50) asc, varchar(15) asc, numeric(19,0) asc. То, что производительность была отличной, несмотря на громоздкий индекс, еще раз демонстрирует, что этот подход работает.

однако очень важно, чтобы предложение order by в Оконная функция Row_Number соответствует индексу. В противном случае производительность снизится до того же уровня, что и в первом примере.

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

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

-- For a sproc, make these your input parameters
declare
    @PageSize int = 50,
    @Page int = 15619;

-- For a sproc, make these your output parameters
declare @RecordCount int = (select count(*) from MyTable);
declare @PageCount int = ceiling(convert(float, @RecordCount) / @PageSize);
declare @Offset int = (@Page - 1) * @PageSize;
declare @LowestRowNumber int = @Offset;
declare @HighestRowNumber int = @Offset + @PageSize - 1;

select
    @RecordCount as RecordCount,
    @PageCount as PageCount,
    @Offset as Offset,
    @LowestRowNumber as LowestRowNumber,
    @HighestRowNumber as HighestRowNumber;

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

на SQL 2012 можно использовать OFFSET и FETCH:

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @N ROWS
FETCH NEXT @M ROWS ONLY;

---

Я лично предпочитаю:

DECLARE @CurrentSetNumber int = 0;
DECLARE @NumRowsInSet int = 2;

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @NumRowsInSet * @CurrentSetNumber ROWS
FETCH NEXT @NumRowsInSet ROWS ONLY;

SET @CurrentSetNumber = @CurrentSetNumber + 1;

здесь @NumRowsInSet это количество строк, которые вы хотите вернуть и @CurrentSetNumber число @NumRowsInSet пропустить.

Если вы хотите выбрать 100 записей из 25-й записи:

select TOP 100 * from TableName
where PrimaryKeyField 
   NOT IN(Select TOP 24 PrimaryKeyField from TableName);

некрасиво, халтурно, но должно работать:

select top(M + N - 1) * from TableName
except
select top(N - 1) * from TableName

вероятно, хорошо для небольших результатов, работает во всех версиях TSQL:

SELECT 
        * 
FROM
     (SELECT TOP (N) * 
      FROM 
            (SELECT TOP (M + N - 1) 
             FROM 
                   Table
             ORDER BY 
                      MyColumn) qasc
      ORDER BY 
               MyColumn DESC) qdesc
 ORDER BY 
         MyColumn

        -- *some* implementations may support this syntax (mysql?)
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 , 0
   ;

        -- Separate LIMIT, OFFSET
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 OFFSET 2
   ;

        -- SQL-2008 syntax
SELECT Id,Value
FROM xxx
ORDER BY Id
OFFSET 4
FETCH NEXT 2 ROWS ONLY
  ;

@start = 3
@records = 2

Select ID, Value 
From
(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum, ID,Value 
From MyTable) as sub
Where sub.RowNum between @start and @start+@records

Это один из способов. есть много других, если вы google SQL подкачки.

этот поток довольно старый, но в настоящее время вы можете сделать это: гораздо чище, имхо

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

источник: http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/

Ниже приведен простой запрос перечислит N строк из M+1-й строки таблицы. Замените M и N предпочтительными номерами.

Select Top N B.PrimaryKeyColumn from 
    (SELECT 
        top M PrimaryKeyColumn
     FROM 
        MyTable
) A right outer join MyTable B 
on 
    A.PrimaryKeyColumn = B.PrimaryKeyColumn
where 
    A.PrimaryKeyColumn IS NULL

пожалуйста, дайте мне знать, полезно ли это для вашей ситуации.

и это, как вы можете достичь той же цели на таблицах без первичный ключ:

select * from
(
    select row_number() over(order by (select 0)) rowNum,*
    from your_table
) tmp
where tmp.rowNum between 20 and 30 -- any numbers you need

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

проходы-это не начальная строка и количество строк, а скорее "строки на страницу (500)" и "номер страницы (4)", которые будут строками 1501 - 2000. Эти значения могут быть заменены переменные хранимой процедуры, поэтому вы не заблокированы в использовании определенного объема подкачки.

select * from (
    select
        (((ROW_NUMBER() OVER(ORDER BY MyField) - 1) / 500) + 1) AS PageNum
        , *
    from MyTable
) as PagedTable
where PageNum = 4;

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

вы не можете использовать ключевое слово "TOP" при этом, вы должны использовать смещение N строк выборки следующих M строк.

пример:

select * from table order by [some_column] 
offset 10 rows
FETCH NEXT 10 rows only

вы можете узнать больше здесь: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx

найти id для строки N Затем получите верхние M строк, которые имеют идентификатор больше или равен этому

declare @N as int
set @N = 2
declare @M as int
set @M = 3

declare @Nid as int

set @Nid = max(id)
from
  (select top @N *
from MyTable
order by id)

select top @M *
from MyTable
where id >= @Nid
order by id

что-то подобное ... но я сделал некоторые предположения здесь (например, вы хотите заказать по id)

существует довольно прямой метод для T-SQL, хотя я не уверен, что это prestanda-эффективно, если вы пропускаете большое количество строк.

SELECT TOP numberYouWantToTake 
    [yourColumns...] 
FROM yourTable 
WHERE yourIDColumn NOT IN (
    SELECT TOP numberYouWantToSkip 
        yourIDColumn 
    FROM yourTable 
    ORDER BY yourOrderColumn
)
ORDER BY yourOrderColumn

Если вы используете .Net, вы можете использовать следующее, например, IEnumerable с вашими результатами данных:

IEnumerable<yourDataType> yourSelectedData = yourDataInAnIEnumerable.Skip(nubmerYouWantToSkip).Take(numberYouWantToTake);

Это зад, что вы получаете все данные из хранилища данных.

почему бы не сделать два запроса:

select top(M+N-1) * from table into temp tmp_final with no log;
select top(N-1) * from tmp_final order by id desc;

SELECT * FROM (
  SELECT
    Row_Number() Over (Order by (Select 1)) as RawKey,
    * 
  FROM [Alzh].[dbo].[DM_THD_TRANS_FY14]
) AS foo
WHERE RawKey between 17210400 and 17210500