SQL Server 的 LIMIT 和 OFFSET 等价物？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2135418/
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
Equivalent of LIMIT and OFFSET for SQL Server?
提问by Earlz
In PostgreSQL there is the Limitand Offsetkeywords which will allow very easy pagination of result sets.
在 PostgreSQL 中有LimitandOffset关键字，可以非常轻松地对结果集进行分页。
What is the equivalent syntax for SQL Server?
SQL Server 的等效语法是什么？
采纳答案by Aaronaught
The equivalent of LIMITis SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:
相当于LIMITis 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
The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).
这里的优点是偏移和限制的参数化，以防您决定更改分页选项（或允许用户这样做）。
Note:the @Offsetparameter should use one-based indexing for this rather than the normal zero-based indexing.
注意：该@Offset参数应为此使用从一开始的索引，而不是正常的从零开始的索引。
回答by Somnath Muluk
This feature is now made easy in SQL Server 2012. This is working from SQL Server 2012 onwards.
此功能现在在 SQL Server 2012 中变得简单。从 SQL Server 2012 开始，它就可以使用。
Limit with offset to select 11 to 20 rows in SQL Server:
使用偏移量限制在 SQL Server 中选择 11 到 20 行：
SELECT email FROM emailTable
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
OFFSET: number of skipped rowsNEXT: required number of next rows
OFFSET: 跳过的行数NEXT: 所需的下一行数
参考：https: //docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017
回答by jorgeu
select top {LIMIT HERE} * from (
select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n
from {YOUR TABLES} where {OTHER OPTIONAL FILTERS}
) xx where r_n_n >={OFFSET HERE}
A note:This solution will only work in SQL Server 2005 or above, since this was when ROW_NUMBER()was implemented.
注意：此解决方案仅适用于 SQL Server 2005 或更高版本，因为这是在ROW_NUMBER()实施时。
回答by Tom H
You can use ROW_NUMBER in a Common Table Expression to achieve this.
您可以在公共表表达式中使用 ROW_NUMBER 来实现此目的。
;WITH My_CTE AS
(
SELECT
col1,
col2,
ROW_NUMBER() OVER(ORDER BY col1) AS row_number
FROM
My_Table
WHERE
<<<whatever>>>
)
SELECT
col1,
col2
FROM
My_CTE
WHERE
row_number BETWEEN @start_row AND @end_row
回答by sebasdev
For me the use of OFFSET and FETCH together was slow, so I used a combination of TOP and OFFSET like this (which was faster):
对我来说，同时使用 OFFSET 和 FETCH 很慢，所以我使用了这样的 TOP 和 OFFSET 组合（速度更快）：
SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname
Note:If you use TOP and OFFSET together in the same query like:
注意：如果您在同一个查询中同时使用 TOP 和 OFFSET，例如：
SELECT TOP 20 columname1, columname2 FROM tablename
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS
Then you get an error, so for use TOP and OFFSET together you need to separate it with a sub-query.
然后你会得到一个错误，所以要一起使用 TOP 和 OFFSET，你需要用子查询将它分开。
And if you need to use SELECT DISTINCT then the query is like:
如果您需要使用 SELECT DISTINCT 那么查询就像：
SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2
WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname
Note:The use of SELECT ROW_NUMBER with DISTINCT did not work for me.
注意：将 SELECT ROW_NUMBER 与 DISTINCT 一起使用对我不起作用。
回答by Tom
Adding a slight variation on Aaronaught's solution, I typically parametrize page number (@PageNum) and page size (@PageSize). This way each page click event just sends in the requested page number along with a configurable page size:
在 Aaronaught 的解决方案上添加一些细微的变化，我通常对页码 (@PageNum) 和页面大小 (@PageSize) 进行参数化。这样每个页面点击事件只发送请求的页码以及可配置的页面大小：
begin
with My_CTE as
(
SELECT col1,
ROW_NUMBER() OVER(ORDER BY col1) AS row_number
FROM
My_Table
WHERE
<<<whatever>>>
)
select * from My_CTE
WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1)
AND @PageNum * @PageSize
end
回答by sillyim
Another sample :
另一个示例：
declare @limit int
declare @offset int
set @offset = 2;
set @limit = 20;
declare @count int
declare @idxini int
declare @idxfim int
select @idxfim = @offset * @limit
select @idxini = @idxfim - (@limit-1);
WITH paging AS
(
SELECT
ROW_NUMBER() OVER (order by object_id) AS rowid, *
FROM
sys.objects
)
select *
from
(select COUNT(1) as rowqtd from paging) qtd,
paging
where
rowid between @idxini and @idxfim
order by
rowid;
回答by keepkeywordspleeeease
回答by user2991730
The closest I could make is
我能做的最接近的是
select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber and ct <= toNumber
Which I guess similar to select * from [db].[dbo].[table] LIMIT 0, 10
我猜这类似于 select * from [db].[dbo].[table] LIMIT 0, 10
回答by Barny
select top (@TakeCount) * --FETCH NEXT
from(
Select ROW_NUMBER() OVER (order by StartDate) AS rowid,*
From YourTable
)A
where Rowid>@SkipCount --OFFSET
