SQL如何实现带有分组逻辑的数据分页_窗口函数配合WHERE过滤-SQL-PHP中文网

分页前必须先分组，否则ROW_NUMBER()会错乱；窗口函数不能用于WHERE子句，须用CTE或子查询先生成行号，再在外层WHERE中按rn过滤，且PARTITION BY与ORDER BY缺一不可。

sql如何实现带有分组逻辑的数据分页_窗口函数配合where过滤

分页前必须先分组，否则 `ROW_NUMBER()` 会错乱

直接在 WHERE 中用窗口函数（比如 ROW_NUMBER() OVER (PARTITION BY ...)）会报错，因为窗口函数不能出现在 WHERE 子句里——SQL 执行顺序是 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY，而窗口函数属于 SELECT 阶段。所以必须把分组+编号逻辑提前到子查询或 CTE 中。

常见错误现象：ERROR: window functions are not allowed in WHERE 或结果每组内序号不从 1 开始、跨组连续编号。

正确做法：用 CTE 或子查询先算出每组内的行号，再在外层 WHERE 过滤该行号
关键点：PARTITION BY 必须明确指定分组字段，ORDER BY 决定组内排序逻辑，两者缺一不可
若分组字段含 NULL，不同数据库行为不一致（PostgreSQL 把 NULL 当作相同值分组，MySQL 8.0 默认不合并），建议显式用 COALESCE(col, 'N/A') 统一处理

PostgreSQL / MySQL 8.0+ 的标准写法（CTE + 行号过滤）

以「按部门分组，取每个部门薪资前 2 名员工」为例，要支持跳过前 N 行、取 M 行（即分页），需两层嵌套：

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT id, name, dept_id, salary
FROM ranked
WHERE rn BETWEEN 3 AND 4;

这里 BETWEEN 3 AND 4 相当于「第 2 页，每页 2 条」；注意不是 rn > 2 AND rn ——虽然等价，但 BETWEEN 更直观且部分优化器对它识别更好。

性能影响：如果 employees 表很大，但只查少数几个部门，应在 dept_id 和 salary 上建联合索引：CREATE INDEX idx_dept_salary ON employees(dept_id, salary DESC);
MySQL 8.0+ 支持 CTE，但低版本只能用派生表（即子查询套子查询），语法略啰嗦但语义一致
PostgreSQL 中若需稳定排序，ORDER BY 后建议补一个唯一字段（如 id），避免相同 salary 导致 ROW_NUMBER() 结果不稳定

`RANK()` 和 `DENSE_RANK()` 在分页时的区别很关键

当组内有并列值（比如两个员工同为最高薪），ROW_NUMBER() 强制分配不同序号，而 RANK() 会跳号，DENSE_RANK() 不跳号。这直接影响「取前 N 名」是否包含并列者。

用 ROW_NUMBER()：严格取前 N 行，不管是否并列；适合「只显示固定数量」场景
用 RANK()：并列第 1 名后，下一个名次是第 3 名；适合「榜单类展示」，但分页时容易漏掉并列者（比如 WHERE rnk 可能返回 3 行）
用 DENSE_RANK()：并列第 1 名后，下一个名次是第 2 名；适合「带并列的 Top N」，但要注意它无法控制总行数上限
真实业务中，若要求「每个部门最多返回 2 人，且允许并列」，得结合 RANK() + 外层限制行数，或改用 QUALIFY（BigQuery / Snowflake 支持）

WHERE 过滤必须放在窗口编号之后，但别忽略原始条件下推

很多人把所有过滤条件都堆在最外层 WHERE，导致窗口函数在全表上计算，性能极差。应该把能下推的条件（尤其是高选择率的）尽量提前到 CTE 内部。

比如只查「2023 年入职的员工」，应写成：

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
  WHERE hire_date >= '2023-01-01'  -- ✅ 提前过滤
)
SELECT * FROM ranked WHERE rn = 1;

而不是：

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked 
WHERE rn = 1 AND hire_date >= '2023-01-01'; -- ❌ 全表算完再过滤