SQL 如何从T-SQL中的排序表中的第M行开始获取N行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/758186/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get N rows starting from row M from sorted table in T-SQL
提问by inazaruk
There is a simple way to get top N rows from any table:
有一种简单的方法可以从任何表中获取前 N 行：
SELECT TOP 10 * FROM MyTable ORDER BY MyColumn
Is there any efficient way to query M rows starting from row N
有没有什么有效的方法可以从第 N 行开始查询 M 行
For example,
例如，
Id Value
1 a
2 b
3 c
4 d
5 e
6 f
And query like this
并像这样查询
SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */
queries 2 rows starting from 3d row, i.e 3d and 4th rows are returned.
从 3d 行开始查询 2 行，即返回 3d 和第 4 行。
回答by Jan Zich
UPDATEIf you you are using SQL 2012 new syntax was added to make this really easy. See Implement paging (skip / take) functionality with this query
更新如果您使用的是 SQL 2012，则添加了新的语法以使其变得非常简单。请参阅使用此查询实现分页（跳过/获取）功能
I guess the most elegant is to use the ROW_NUMBER function (available from MS SQL Server 2005):
我想最优雅的是使用 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
回答by Daniel Barbalace
The problem with the suggestions in this thread and elsewhere on the web is that all the proposed solutions run in linear time with respect to the number of records. For example, consider a query like the following.
该线程和网络上其他地方的建议的问题在于，所有建议的解决方案都以相对于记录数量的线性时间运行。例如，考虑如下查询。
select *
from
(
select
Row_Number() over (order by ClusteredIndexField) as RowNumber,
*
from MyTable
) as PagedTable
where RowNumber between @LowestRowNumber and @HighestRowNumber;
When getting page 1, the query takes 0.577 seconds. However, when getting page 15,619, this same query takes over 2 minutes and 55 seconds.
获取第 1 页时，查询需要 0.577 秒。但是，当获得第 15,619 页时，同样的查询需要 2 分 55 秒。
We can greatly improve this by creating a record number, index cross-table as shown in the following query. The cross-table is called PagedTable and is non-persistent.
我们可以通过创建记录号、索引交叉表来大大改善这一点，如下面的查询所示。交叉表称为 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;
Like in the previous example, I tested this on a very wide table with 780,928 records. I used a page size of 50, which resulted in 15,619 pages.
与前面的示例一样，我在一个包含 780,928 条记录的非常宽的表上对此进行了测试。我使用的页面大小为 50，结果为 15,619 页。
The total time taken for page 1 (the first page) is 0.413 seconds. The total time taken for page 15,619 (the last page) is 0.987 seconds, merely twice times as long as page 1. These times were measured using SQL Server Profiler and the DBMS was SQL Server 2008 R2.
第 1 页（第一页）的总时间为 0.413 秒。第 15,619 页（最后一页）花费的总时间为 0.987 秒，仅为第 1 页的两倍。这些时间是使用 SQL Server Profiler 测量的，DBMS 是 SQL Server 2008 R2。
This solution works for any case when you are sorting your table by an index. The index does not have to be clustered or simple. In my case, the index was composed of three fields: varchar(50) asc, varchar(15) asc, numeric(19,0) asc. That the performance was excellent despite the cumbersome index just further demonstrates that this approach works.
当您按索引对表进行排序时，此解决方案适用于任何情况。索引不必是聚集的或简单的。就我而言，索引由三个字段组成：varchar(50) asc、varchar(15) asc、numeric(19,0) asc。尽管索引繁琐，但性能非常好，这进一步证明了这种方法是有效的。
However, it is critical that the order by clause in the Row_Number windowing function corresponds to an index. Otherwise performance will degrade to the same level as the first example.
但是，Row_Number 窗口函数中的 order by 子句与索引对应是至关重要的。否则性能将下降到与第一个示例相同的水平。
This approach does still require a linear operation to generate the non-persistent cross-table, but since that's just an index with a row number added, it happens very quickly. In my case it took 0.347 seconds, but my case had varchars that needed to be copied. A single numeric index would take far less time.
这种方法仍然需要一个线性操作来生成非持久性交叉表，但由于这只是一个添加了行号的索引，它发生得非常快。在我的情况下，它花了 0.347 秒，但我的情况有需要复制的 varchars。单个数字索引将花费更少的时间。
For all practical purposes, this design reduces the scaling of server-side paging from a linear operation to a logarithmic operation allowing the scaling of large tables. Below is the complete solution.
出于所有实际目的，此设计将服务器端分页的扩展从线性操作减少到允许扩展大表的对数操作。下面是完整的解决方案。
-- 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;
回答by Trisped
In SQL 2012you can use OFFSETand FETCH:
在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;
where @NumRowsInSetis the number of rows you want returned and @CurrentSetNumberis the number of @NumRowsInSetto skip.
where@NumRowsInSet是要返回的行@CurrentSetNumber数，是@NumRowsInSet要跳过的行数。
回答by Prashant
If you want to select 100 records from 25th record:
如果要从第 25 条记录中选择 100 条记录：
select TOP 100 * from TableName
where PrimaryKeyField
NOT IN(Select TOP 24 PrimaryKeyField from TableName);
回答by Harper Shelby
Ugly, hackish, but should work:
丑陋，骇人听闻，但应该有效：
select top(M + N - 1) * from TableName
except
select top(N - 1) * from TableName
回答by Steven Kraninger
Probably good for small results, works in all versions of TSQL:
可能适用于小结果，适用于所有版本的 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
回答by wildplasser
-- *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
;
回答by Sander Kouwenhoven
This thread is quite old, but currently you can do this: much cleaner imho
这个线程很旧，但目前你可以这样做：imho更干净
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/
回答by Felipe V. R.
In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.
为了在 SQL Server 中执行此操作，您必须按列对查询进行排序，以便您可以指定所需的行。
You can't use the "TOP" keyword when doing this, you must use offset N rows fetch next M rows.
执行此操作时不能使用“TOP”关键字，必须使用偏移 N 行获取下 M 行。
Example:
例子：
select * from table order by [some_column]
offset 10 rows
FETCH NEXT 10 rows only
You can learn more here: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx
您可以在此处了解更多信息：https: //technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx
回答by Jeremy
@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
This is one way. there are a lot of others if you google SQL Paging.
这是一种方式。如果你谷歌 SQL 分页，还有很多其他的。
