Como obter N linhas a partir da linha M da tabela ordenada em T-SQL

Existe uma maneira simples de obter as principais linhas N de qualquer tabela:

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn 

Existe alguma maneira eficiente de consultar M linhas a partir da linha N

Por exemplo,

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

E consulta assim

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

consulta 2 linhas a partir da linha 3d, ou seja, as linhas 3d e 4 são retornadas.

ATUALIZAÇÃO Se você está usando o SQL 2012, uma nova syntax foi adicionada para tornar isso realmente fácil. Consulte Implementar a funcionalidade de paginação (ignorar / tirar) com essa consulta

Eu acho que o mais elegante é usar a function ROW_NUMBER (disponível no 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 

O problema com as sugestões neste thread e em outros lugares na web é que todas as soluções propostas são executadas em tempo linear com relação ao número de registros. Por exemplo, considere uma consulta como a seguinte.

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

Ao obter a página 1, a consulta demora 0,577 segundos. No entanto, ao obter a página 15,619, essa mesma consulta leva mais de 2 minutos e 55 segundos.

Podemos melhorar muito isso criando um número de registro, indexe a tabela cruzada conforme mostrado na consulta a seguir. A tabela cruzada é chamada PagedTable e não é persistente.

 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; 

Como no exemplo anterior, testei isso em uma tabela muito ampla com 780.928 registros. Eu usei um tamanho de página de 50, o que resultou em 15.619 páginas.

O tempo total gasto para a página 1 (a primeira página) é de 0,413 segundo. O tempo total gasto para a página 15.619 (a última página) é de 0,987 segundos, meramente duas vezes desde a página 1. Esses tempos foram medidos usando o SQL Server Profiler e o DBMS foi o SQL Server 2008 R2.

Essa solução funciona para qualquer caso quando você está classificando sua tabela por um índice. O índice não precisa estar em cluster ou simples. No meu caso, o índice era composto de três campos: varchar (50) asc, varchar (15) asc, numérico (19,0) asc. Que o desempenho foi excelente, apesar do incômodo índice, demonstra que essa abordagem funciona.

No entanto, é essencial que a cláusula order by na function de janelamento Row_Number corresponda a um índice. Caso contrário, o desempenho será reduzido ao mesmo nível do primeiro exemplo.

Essa abordagem ainda requer uma operação linear para gerar a tabela cruzada não persistente, mas como isso é apenas um índice com um número de linha adicionado, isso acontece muito rapidamente. No meu caso demorou 0.347 segundos, mas meu caso tinha varchars que precisavam ser copiados. Um único índice numérico levaria muito menos tempo.

Para todos os fins práticos, esse design reduz o dimensionamento de paginação do lado do servidor de uma operação linear para uma operação logarítmica permitindo o dimensionamento de tabelas grandes. Abaixo está a solução completa.

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

No SQL 2012, você pode usar OFFSET e FETCH :

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

Eu pessoalmente prefiro:

 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; 

onde @NumRowsInSet é o número de linhas que você deseja retornar e @CurrentSetNumber é o número de @NumRowsInSet a ser ignorado.

Se você quiser selecionar 100 registros do 25º registro:

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

Feio, hackish, mas deve funcionar:

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

Provavelmente bom para pequenos resultados, funciona em todas as versões do 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 

Este é um caminho. há muitos outros se você pesquisar no Google SQL.

Este tópico é bem antigo, mas atualmente você pode fazer isso: muito mais limpo

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

fonte: http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/

A seguir, a consulta simples listará N linhas da M + 1ª linha da tabela. Substitua M e N pelos seus números preferidos.

 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 

Por favor, deixe-me saber se isso é útil para sua situação.

E é assim que você pode alcançar o mesmo objective em tabelas sem chave primária:

 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 

Eu li todas as respostas aqui e finalmente encontrei uma solução utilizável que é simples. Os problemas de desempenho surgem da instrução BETWEEN, não da geração dos próprios números de linhas. Então usei um algoritmo para fazer paginação dinâmica passando o número da página e o número de registros.

As passagens não são linha de início e número de linhas, mas sim “linhas por página (500)” e “número de página (4)” que seriam linhas 1501 – 2000. Esses valores podem ser substituídos por variables ​​de procedimento armazenado para que você não seja bloqueado em usar um valor de paginação específico.

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

Para fazer isso no SQL Server, você deve solicitar a consulta por uma coluna, para poder especificar as linhas desejadas.

Você não pode usar a palavra-chave “TOP” ao fazer isso, você deve usar as linhas de offset N buscar as próximas linhas M.

Exemplo:

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

Você pode aprender mais aqui: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx

Encontre o id da linha N Então pegue as linhas M superiores que possuem um id maior ou igual a esse

 declarar @N como int
 definir @N = 2
 declarar @M como int
 definir @M = 3

 declarar @Nid como int

 set @Nid = max (id)
 a partir de
   (selecione o topo @N *
 de MyTable
 ordem por id)

 selecione o topo @M *
 de MyTable
 onde id> = @Nid
 ordem por id

Algo assim … mas eu fiz algumas suposições aqui (por exemplo, você quer encomendar por id)

Existe um método bastante direto para o T-SQL , embora eu não tenha certeza se ele é eficaz se você estiver pulando um grande número de linhas.

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

Se você estiver usando o .Net, poderá usar o seguinte em um IEnumerable com seus resultados de dados:

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

Isso tem o lado negativo de que você está recebendo todos os dados do armazenamento de dados.

Por que não fazer duas consultas:

 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