SQL Server 中的限制为 10..20
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/971964/
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
LIMIT 10..20 in SQL Server
提问by marcgg
I'm trying to do something like :
我正在尝试做类似的事情：
SELECT * FROM table LIMIT 10,20
or
或者
SELECT * FROM table LIMIT 10 OFFSET 10
but using SQL Server
但使用 SQL Server
The only solution I foundlooks like overkill:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases
) a WHERE row > 5 and row <= 10
I also found:
我还发现：
SELECT TOP 10 * FROM stuff;
... but it's not what I want to do since I can't specify the starting limit.
...但这不是我想要做的，因为我无法指定起始限制。
Is there another way for me to do that ?
我还有其他方法可以做到这一点吗？
Also, just curious, is there a reason why doesn't SQL Server support the LIMITfunction or something similar? I don't want to be mean, but that really sounds like something a DBMS needs ... If it does, then I'm sorry for being so ignorant! I've been working with MySQL and SQL+ for the past 5 years so...
另外，只是好奇，是否有原因 SQL Server 不支持该LIMIT功能或类似的功能？我不想刻薄，但这听起来确实是 DBMS 需要的东西......如果是这样，那么我很抱歉我这么无知！在过去的 5 年里，我一直在使用 MySQL 和 SQL+，所以......
采纳答案by Bill Karwin
The LIMITclause is not part of standard SQL. It's supported as a vendor extension to SQL by MySQL, PostgreSQL, and SQLite.
该LIMIT子句不是标准 SQL 的一部分。MySQL、PostgreSQL 和 SQLite 支持它作为 SQL 的供应商扩展。
Other brands of database may have similar features (e.g. TOPin Microsoft SQL Server), but these don't always work identically.
其他品牌的数据库可能具有类似的功能（例如TOP在 Microsoft SQL Server 中），但它们的工作方式并不总是相同。
It's hard to use TOPin Microsoft SQL Server to mimic the LIMITclause. There are cases where it just doesn't work.
很难TOP在 Microsoft SQL Server 中使用来模仿LIMIT子句。有些情况下它不起作用。
The solution you showed, using ROW_NUMBER()is available in Microsoft SQL Server 2005 and later. This is the best solution (for now) that works solely as part of the query.
您展示的解决方案ROW_NUMBER()在 Microsoft SQL Server 2005 及更高版本中可用。这是仅作为查询的一部分工作的最佳解决方案（目前）。
Another solution is to use TOPto fetch the first count+ offsetrows, and then use the API to seek past the first offsetrows.
另一种解决方案是使用TOP获取第一个count+ offset行，然后使用 API 寻找第一个offset行。
See also:
也可以看看：
回答by Martin Smith
For SQL Server 2012 + you can use.
对于 SQL Server 2012 +，您可以使用.
SELECT *
FROM sys.databases
ORDER BY name
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY
回答by KM.
as you found, this is the preferred sql server method:
如您所见，这是首选的 sql server 方法：
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases
) a WHERE a.row > 5 and a.row <= 10
回答by Jodrell
If you are using SQL Server 2012+ vote for Martin Smith's answerand use the OFFSETand FETCH NEXTextensions to ORDER BY,
如果您使用 SQL Server 2012+ 投票支持Martin Smith 的答案并使用OFFSET和FETCH NEXT扩展名ORDER BY，
If you are unfortunate enough to be stuck with an earlier version, you could do something like this,
如果你不幸被早期版本卡住了，你可以做这样的事情，
WITH Rows AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dbo].[SomeColumn]) [Row]
, *
FROM
[dbo].[SomeTable]
)
SELECT TOP 10
*
FROM
Rows
WHERE Row > 10
I believe is functionaly equivalent to
我相信在功能上等同于
SELECT * FROM SomeTable LIMIT 10 OFFSET 10 ORDER BY SomeColumn
and the best performing way I know of doing it in TSQL, before MS SQL 2012.
以及我所知道的在 MS SQL 2012 之前在 TSQL 中执行此操作的最佳表现方式。
If there are very many rows you may get better performance using a temp table instead of a CTE.
如果有很多行，您可以使用临时表而不是 CTE 获得更好的性能。
回答by Joel Coehoorn
Unfortunately, the ROW_NUMBER()is the best you can do. It's actually more correct, because the results of a limitor topclause don't really have meaning without respect to some specific order. But it's still a pain to do.
不幸的是，这ROW_NUMBER()是你能做的最好的。它实际上更正确，因为limitortop子句的结果在不考虑某些特定顺序的情况下并没有真正的意义。但是做起来还是很痛苦的。
Update:Sql Server 2012 adds a limit-like feature via OFFSET and FETCH keywords. This is the ansi-standard approach, as opposed to LIMIT, which is a non-standard MySql extension.
更新：Sql Server 2012limit通过OFFSET 和 FETCH 关键字添加了类似功能。这是 ansi-standard 方法，而不是LIMIT，这是一个非标准的 MySql 扩展。
回答by David Patrick
How about this?
这个怎么样？
SET ROWCOUNT 10
SELECT TOP 20 *
FROM sys.databases
ORDER BY database_id DESC
It gives you the last 10 rows of the first 20 rows. One drawback is that the order is reversed, but, at least it's easy to remember.
它为您提供前 20 行中的最后 10 行。一个缺点是顺序颠倒了，但是，至少它很容易记住。
回答by Andy
SELECT TOP 10 *
FROM TABLE
WHERE IDCOLUMN NOT IN (SELECT TOP 10 IDCOLUMN FROM TABLE)
Should give records 11-20. Probably not too efficient if incrementing to get further pages, and not sure how it might be affected by ordering. Might have to specify this in both WHERE statements.
应该给记录11-20。如果递增以获取更多页面，则可能效率不高，并且不确定排序会如何影响它。可能必须在两个 WHERE 语句中指定此项。
回答by Wahaj Latif
A good way is to create a procedure:
一个好方法是创建一个过程：
create proc pagination (@startfrom int ,@endto int) as
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY name desc) as row FROM sys.databases
) a WHERE a.row > @startfrom and a.row <= @endto
just like limit 0,2 /////////////// execute pagination 0,4
就像 limit 0,2 /////////////// 执行分页 0,4
回答by Y.B.
Just for the record solution that works across most database engines though might not be the most efficient:
仅适用于适用于大多数数据库引擎的记录解决方案，但可能不是最有效的：
Select Top (ReturnCount) *
From (
Select Top (SkipCount + ReturnCount) *
From SourceTable
Order By ReverseSortCondition
) ReverseSorted
Order By SortCondition
Pelase note: the last page would still contain ReturnCount rows no matter what SkipCount is. But that might be a good thing in many cases.
Pelase 注意：无论 SkipCount 是什么，最后一页仍将包含 ReturnCount 行。但这在许多情况下可能是一件好事。
回答by Satish Kumar sonker
The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:
LIMIT 的等价物是 SET ROWCOUNT，但如果您想要通用分页，最好编写如下查询：
;WITH Results_CTE AS
(
SELECT
Col1, Col2, ...,
ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
FROM Table
WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit
