如何用Window Function实现按分组的Limit+Offset分页？

核心思路 #

要实现按部门分组分页（每次返回指定数量的部门及其所有行），关键是先给每个唯一部门分配一个全局排序的序号，再根据limit和offset筛选出目标部门，最后关联原表获取这些部门的全部数据。

具体实现（以PostgreSQL为例） #

假设你的业务表名为employees，包含depname（部门名）、empno（员工编号）、salary（薪资）等字段。

1. 给部门分配全局排序序号 #

先通过窗口函数给每个唯一部门生成一个排序后的序号，这里按部门名称升序排序（你可以根据需求替换排序规则，比如按部门总薪资排序）：

WITH ranked_departments AS (
    SELECT DISTINCT depname,
           ROW_NUMBER() OVER (ORDER BY depname) AS dept_rank
    FROM employees
)
SELECT * FROM ranked_departments;

2. 结合limit和offset筛选目标部门并关联原表 #

根据offset和limit计算目标部门的序号范围，再关联原表获取这些部门的所有行数据：

-- 替换$1为offset值，$2为limit值（比如offset=0、limit=2时，$1=0，$2=2）
WITH ranked_departments AS (
    SELECT DISTINCT depname,
           ROW_NUMBER() OVER (ORDER BY depname) AS dept_rank
    FROM employees
)
SELECT e.*
FROM employees e
JOIN ranked_departments rd ON e.depname = rd.depname
WHERE rd.dept_rank BETWEEN $1 + 1 AND $1 + $2;

示例场景

• 当offset=0、limit=2时，筛选dept_rank在1~2之间的部门，返回develop和personnel的全部行；
• 当offset=1、limit=2时，筛选dept_rank在3~4之间的部门，返回sales和tech的全部行。

3. 自定义部门排序规则 #

如果需要按部门的聚合指标排序（比如部门总薪资降序），只需修改窗口函数的ORDER BY子句：

WITH ranked_departments AS (
    SELECT DISTINCT depname,
           ROW_NUMBER() OVER (ORDER BY (SELECT SUM(salary) FROM employees e2 WHERE e2.depname = e1.depname) DESC) AS dept_rank
    FROM employees e1
)
SELECT e.*
FROM employees e
JOIN ranked_departments rd ON e.depname = rd.depname
WHERE rd.dept_rank BETWEEN $1 + 1 AND $1 + $2;

为什么直接用ROW_NUMBER()在原表不行？ #

直接在原表的每行上用ROW_NUMBER()，会给同一个部门的不同行分配不同的序号，无法实现"一次性获取整个部门所有行"的需求。我们需要的是给部门而非行分配全局序号，再基于部门序号做分页。

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