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

Существует простой способ получить верхние N строк из любой таблицы:

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn

Есть ли эффективный способ запроса M строк, начиная с строки N

Например,

Id Value
1    a
2    b
3    c
4    d
5    e
6    f

И запрос вроде этого

SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */

запрашивает 2 строки, начиная с 3d строки, т.е. возвращаются 3d и 4 строки.

Ответы

Ответ 1

UPDATE. Если вы используете SQL 2012, новый синтаксис был добавлен, чтобы сделать это очень просто. См. Функцию подкачки (skip/take) с этим запросом

Я думаю, наиболее элегантным является использование функции ROW_NUMBER (доступной из MS SQL Server 2005):

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To

Ответ 2

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

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,877 секунды, что в два раза больше, чем страница 1. Эти времена были измерены с использованием SQL Server Profiler, а СУБД - 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;

Ответ 3

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

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

Ответ 4

В 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 для пропуска.

Ответ 5

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

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

Ответ 6

Вероятно, хорош для небольших результатов, работает во всех версиях 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

Ответ 7

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

Ответ 8

@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 @[email protected]

Это один из способов. есть много других, если вы используете Google Paging.

Ответ 9

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

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/

Ответ 10

Ниже приведен простой запрос будет перечислять 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

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

Ответ 11

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

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

Ответ 12

Я прочитал все ответы здесь и, наконец, придумал полезное решение, которое просто. Проблемы с производительностью возникают из инструкции 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;

Ответ 13

Чтобы сделать это в 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

Ответ 14

Найти идентификатор для строки 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)

Ответ 15

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

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

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

Ответ 16

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

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;

Ответ 17

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