SQL Server 的 LIMIT 和 OFFSET 等价物?

2021-12-01 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 参数应该为此使用从一开始的索引，而不是正常的从零开始的索引.

Note: the @Offset parameter should use one-based indexing for this rather than the normal zero-based indexing.

相关文章