SQLServer中获取指定范围分页取数的两种方式

1. ROW_NUMBER() + CTE (推荐)

这是最常用且性能较好的分页方式，支持 SQL Server 2005 及以上版本。

-- 方式一：使用 ROW_NUMBER() + CTE
WITH PaginatedData AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNum,
        UserID,
        UserName,
        Email,
        CreateDate
    FROM Users
    WHERE IsActive = 1  -- 可选的筛选条件
)
SELECT *
FROM PaginatedData
WHERE RowNum BETWEEN 11 AND 20  -- 获取第2页，每页10条
ORDER BY RowNum;

优点：

性能较好，SQL Server 能有效优化
支持复杂的排序规则
可以包含 WHERE 条件进行筛选
代码清晰易读

2. OFFSET FETCH (SQL Server 2012+)

这是 SQL Server 2012 引入的 ANSI SQL 标准语法，语法更简洁。

-- 方式二：使用 OFFSET FETCH
SELECT 
    UserID,
    UserName,
    Email,
    CreateDate
FROM Users
WHERE IsActive = 1  -- 可选的筛选条件
ORDER BY CreateDate DESC
OFFSET 10 ROWS       -- 跳过前10条（第1页）
FETCH NEXT 10 ROWS ONLY;  -- 取10条（第2页）

优点：

语法简洁，符合 ANSI SQL 标准
在 SQL Server 2012+ 中性能良好
可读性高

对比与选择建议

特性	ROW_NUMBER() + CTE	OFFSET FETCH
版本支持	SQL Server 2005+	SQL Server 2012+
性能	良好	良好（2012+）
语法简洁性	中等	高
灵活性	高（可做复杂处理）	中等
标准性	非标准	ANSI SQL 标准

实用封装示例

1. 存储过程封装

CREATE PROCEDURE GetUsersByPage
    @PageIndex INT = 1,      -- 页码（从1开始）
    @PageSize INT = 10,       -- 每页条数
    @SortField NVARCHAR(50) = 'CreateDate',
    @SortOrder NVARCHAR(4) = 'DESC'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @StartRow INT = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @EndRow INT = @PageIndex * @PageSize;

    -- 动态排序需要用动态SQL，这里用 ROW_NUMBER()
    DECLARE @Sql NVARCHAR(MAX);
    SET @Sql = N'
    WITH PaginatedData AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@SortField) + ' ' + @SortOrder + ') AS RowNum,
            UserID,
            UserName,
            Email,
            CreateDate
        FROM Users
        WHERE IsActive = 1
    )
    SELECT *
    FROM PaginatedData
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    ORDER BY RowNum';

    EXEC sp_executesql @Sql, 
        N'@StartRow INT, @EndRow INT',
        @StartRow, @EndRow;
END

2. 获取总记录数 + 分页数据

-- 同时返回分页数据和总记录数
DECLARE @PageIndex INT = 2;
DECLARE @PageSize INT = 10;

-- 使用 OUTPUT 参数或额外查询获取总数
DECLARE @TotalCount INT;

SELECT @TotalCount = COUNT(*)
FROM Users
WHERE IsActive = 1;

WITH PaginatedData AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNum,
        UserID,
        UserName,
        Email,
        CreateDate
    FROM Users
    WHERE IsActive = 1
)
SELECT 
    *,
    @TotalCount AS TotalCount
FROM PaginatedData
WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 
    AND @PageIndex * @PageSize;

性能优化建议

索引优化：确保 ORDER BY 和 WHERE 涉及的列有合适的索引 **避免 SELECT ***：只选择需要的列 参数化查询：防止 SQL 注入 大表分页：对于非常大的表，可以考虑使用键集分页

-- 键集分页（保持分页状态）
SELECT TOP 10 *
FROM Users
WHERE CreateDate < @LastSeenDate  -- 上一页最后一条的日期
ORDER BY CreateDate DESC;

选择建议

如果使用 SQL Server 2012+，推荐使用 OFFSET FETCH（语法简洁）
如果需要支持 SQL Server 2005/2008，使用 ROW_NUMBER()
对于需要动态排序的场景，ROW_NUMBER() 更方便
对于超大数据集，考虑使用键集分页替代