相当于 SQL Server 的 LIMIT 和 OFFSET?
在 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 开始的索引,而不是正常的从 0 开始的索引.
Note: the @Offset
parameter should use one-based indexing for this rather than the normal zero-based indexing.
相关文章