SQL Server 中的行偏移
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/187998/
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
Row Offset in SQL Server
提问by Alex
Is there any way in SQL Server to get the results starting at a given offset? For example, in another type of SQL database, it's possible to do:
SQL Server 中是否有任何方法可以从给定的偏移量开始获取结果？例如，在另一种类型的 SQL 数据库中，可以执行以下操作：
SELECT * FROM MyTable OFFSET 50 LIMIT 25
to get results 51-75. This construct does not appear to exist in SQL Server.
得到结果 51-75。SQL Server 中似乎不存在此构造。
How can I accomplish this without loading all the rows I don't care about? Thanks!
如何在不加载我不关心的所有行的情况下完成此操作？谢谢！
回答by Brian Kim
I would avoid using SELECT *. Specify columns you actually want even though it may be all of them.
我会避免使用SELECT *. 指定您实际需要的列，即使它可能是所有列。
SQL Server 2005+
SQL Server 2005+
SELECT col1, col2
FROM (
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow
SQL Server 2000
SQL Server 2000
Efficiently Paging Through Large Result Sets in SQL Server 2000
A More Efficient Method for Paging Through Large Result Sets
回答by Martin Smith
If you will be processing all pages in order then simply remembering the last key value seen on the previous page and using TOP (25) ... WHERE Key > @last_key ORDER BY Keycan be the best performing method if suitable indexes exist to allow this to be seeked efficiently - or an API cursorif they don't.
如果您将按顺序处理所有页面，那么只需记住在上一页上看到的最后一个键值，TOP (25) ... WHERE Key > @last_key ORDER BY Key如果存在合适的索引以允许有效查找，则使用可能是性能最佳的方法 -如果没有，则使用 API 游标.
For selecting an arbitary page the best solution for SQL Server 2005 - 2008 R2 is probably ROW_NUMBERand BETWEEN
对于选择arbitary页面的最佳解决方案为SQL Server 2005 - 2008 R2可能是ROW_NUMBER和BETWEEN
For SQL Server 2012+ you can use the enhanced ORDER BYclause for this need.
对于 SQL Server 2012+，您可以使用增强的ORDER BY子句来满足此需求。
SELECT *
FROM MyTable
ORDER BY OrderingColumn ASC
OFFSET 50 ROWS
FETCH NEXT 25 ROWS ONLY
Though it remains to be seen how well performing this option will be.
回答by leoinfo
This is one way (SQL2000)
这是一种方式（SQL2000）
SELECT * FROM
(
SELECT TOP (@pageSize) * FROM
(
SELECT TOP (@pageNumber * @pageSize) *
FROM tableName
ORDER BY columnName ASC
) AS t1
ORDER BY columnName DESC
) AS t2
ORDER BY columnName ASC
and this is another way (SQL 2005)
这是另一种方式（SQL 2005）
;WITH results AS (
SELECT
rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
, *
FROM tableName
)
SELECT *
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize
回答by Matthias Meid
You can use ROW_NUMBER()function to get what you want:
您可以使用ROW_NUMBER()函数来获得您想要的：
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t
WHERE RowNr BETWEEN 10 AND 20
回答by Lukas Eder
There is OFFSET .. FETCHin SQL Server 2012, but you will need to specify an ORDER BYcolumn.
有OFFSET .. FETCH在SQL Server 2012中，但你需要指定一个ORDER BY列。
If you really don't have any explicit column that you could pass as an ORDER BYcolumn (as others have suggested), then you can use this trick:
如果您确实没有任何可以作为ORDER BY列传递的显式列（正如其他人所建议的那样），那么您可以使用以下技巧：
SELECT * FROM MyTable
ORDER BY @@VERSION
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY
... or
... 或者
SELECT * FROM MyTable
ORDER BY (SELECT 0)
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY
We're using it in jOOQwhen users do not explicitly specify an order. This will then produce pretty random ordering without any additional costs.
当用户没有明确指定订单时，我们在jOOQ 中使用它。这将产生非常随机的排序，而无需任何额外成本。
回答by Arthur van Dijk
For tables with more and large data columns, I prefer:
对于具有更多和大数据列的表，我更喜欢：
SELECT
tablename.col1,
tablename.col2,
tablename.col3,
...
FROM
(
(
SELECT
col1
FROM
(
SELECT col1, ROW_NUMBER() OVER (ORDER BY col1 ASC) AS RowNum
FROM tablename
WHERE ([CONDITION])
)
AS T1 WHERE T1.RowNum BETWEEN [OFFSET] AND [OFFSET + LIMIT]
)
AS T2 INNER JOIN tablename ON T2.col1=tablename.col1
);
-
——
[CONDITION] can contain any WHERE clause for searching.
[OFFSET] specifies the start,
[LIMIT] the maximum results.
It has much better performance on tables with large data like BLOBs, because the ROW_NUMBER function only has to look through one column, and only the matching rows are returned with all columns.
它在具有大数据（如 BLOB）的表上具有更好的性能，因为 ROW_NUMBER 函数只需查看一列，并且所有列仅返回匹配的行。
回答by PerfectLion
See my select for paginator
查看我的分页器选择
SELECT TOP @limit * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY colunx ASC) offset, * FROM (
-- YOU SELECT HERE
SELECT * FROM mytable
) myquery
) paginator
WHERE offset > @offset
This solves the pagination ;)
这解决了分页问题;)
回答by Jithin Shaji
SELECT TOP 75 * FROM MyTable
EXCEPT
SELECT TOP 50 * FROM MyTable
回答by Shb
Following will display 25 records excluding first 50 records works in SQL Server 2012.
以下将显示 25 条记录，不包括 SQL Server 2012 中的前 50 条记录。
SELECT * FROM MyTable ORDER BY ID OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
you can replace ID as your requirement
您可以根据需要更换 ID
回答by Unsliced
Depending on your version ou cannot do it directly, but you could do something hacky like
根据你的版本你不能直接做，但你可以做一些像
select top 25 *
from (
select top 75 *
from table
order by field asc
) a
order by field desc
where 'field' is the key.
其中“字段”是关键。
