SQL Server 超80万记录表按列分组后按组分页实现方案咨询

核心实现逻辑 #

• 用ROW_NUMBER()窗口函数，给每个分组内的行按指定排序规则生成连续序号
• 基于分组内的序号计算该条数据所属的页码：CEILING(分组内序号 / 单页每个分组最大展示条数)
• 筛选出页码等于目标页码的所有数据即可

示例代码（对应你给出的Color分组样例） #

-- 入参定义
DECLARE @TargetPage INT = 1; -- 要查询的页码
DECLARE @PerGroupMaxRows INT = 2; -- 单页每个分组最多展示条数，你的场景可以改成50

WITH GroupedRowMark AS (
    SELECT 
        Id,
        Color,
        Name,
        -- PARTITION BY后填分组列，ORDER BY后填分组内的排序规则，可按需调整
        ROW_NUMBER() OVER (PARTITION BY Color ORDER BY Id ASC) AS GroupInnerNo
    FROM 你的表名
)
SELECT Id, Color, Name
FROM GroupedRowMark
-- 乘以1.0是为了避免整数除法舍去小数，导致CEILING计算错误
WHERE CEILING(GroupInnerNo * 1.0 / @PerGroupMaxRows) = @TargetPage
-- 最终结果排序规则可按需调整
ORDER BY Color ASC, Id ASC;

效果验证 #

对应你的样例数据：

• Red分组内序号为1、2、3，计算后1、2属于第1页，3属于第2页
• Green分组内序号为1、2、3、4，计算后1、2属于第1页，3、4属于第2页
  完全符合你要求的分页展示规则。

适配你原有[Order]表的改造代码 #

DECLARE @TargetPage INT = 1;
DECLARE @PerGroupMaxRows INT = 50;

WITH OrderGroupMark AS (
    SELECT 
        *,
        -- PARTITION BY后替换为你实际要分组的列，ORDER BY保持你原有排序规则即可
        ROW_NUMBER() OVER (PARTITION BY [From] ORDER BY [From] ASC) AS GroupInnerNo
    FROM [Order]
)
SELECT *
FROM OrderGroupMark
WHERE CEILING(GroupInnerNo * 1.0 / @PerGroupMaxRows) = @TargetPage
ORDER BY [From] ASC;

性能优化建议 #

你的表数据量超过80万，建议给分组列 + 分组内排序的列建立联合索引，可避免全表扫描和额外排序开销，大幅提升查询效率。

内容的提问来源于stack exchange，提问作者Ali Bdeir