相当于 SQL Server 的 LIMIT 和 OFFSET?

2022-01-30 00:00:00 sql limit offset sql-server pagination

在 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.

相关文章