等价于SQL Server的LIMIT和OFFSET? [英] Equivalent of LIMIT and OFFSET for SQL Server?
问题描述
在PostgreSQL中有Limit
和Offset
关键字,它们可以非常容易地分页结果集.
In PostgreSQL there is the Limit
and Offset
keywords which will allow very easy pagination of result sets.
SQL Server的等效语法是什么?
What is the equivalent syntax for SQL Server?
推荐答案
LIMIT
的等效项是SET ROWCOUNT
,但是如果要进行通用分页,最好编写这样的查询:
The equivalent of LIMIT
is SET ROWCOUNT
, but if you want generic pagination it's better to write a query like this:
;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).
注意:@Offset
参数应为此使用基于1的索引,而不是基于零的常规索引.
Note: the @Offset
parameter should use one-based indexing for this rather than the normal zero-based indexing.
这篇关于等价于SQL Server的LIMIT和OFFSET?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!