怎样在SQL中结合窗口函数和CTE递归生成动态排名-SQL-PHP中文网

递归cte中禁止使用窗口函数，因语义冲突且sql标准强制限制；必须先用递归生成带层级/路径的原始数据，再在外层查询中应用row_number()等窗口函数。

怎样在sql中结合窗口函数和cte递归生成动态排名

递归CTE里不能直接用窗口函数，这是硬限制

SQL标准规定，WITH RECURSIVE 的递归成员（即 UNION ALL 右侧部分）中禁止出现窗口函数。你如果在递归查询里写 ROW_NUMBER() OVER (...) 或 RANK()，PostgreSQL、SQL Server、SQLite 都会报错，比如 PostgreSQL 报 ERROR: window functions are not allowed in recursive queries。这不是语法疏忽，而是因为窗口函数依赖完整结果集排序，而递归执行是逐层生成的，二者语义冲突。

解决思路很明确：把窗口函数“挪到递归之外”，也就是先用递归 CTE 生成带层级/顺序的原始数据，再在外层查询中加窗口函数做排名。

递归 CTE 只负责生成逻辑序列（如组织树路径、时间序列展开、编号补全等）
给每行打上可排序的字段，例如 level、path、seq 或自增 id
外层 SELECT 对这个结果集应用 ROW_NUMBER()、DENSE_RANK() 等

用 level + path 构建稳定排序依据

很多场景下，递归生成的数据天然有层次结构（比如部门上下级、BOM 展开、评论回复链）。这时别依赖数据库默认返回顺序——它不保证稳定。必须显式构造能唯一排序的字段。

推荐组合：level（递归深度）+ path（字符串路径，如 '1.3.7'），或用数组（PostgreSQL）/JSON（MySQL 8.0+）存路径。这样外层按 path 排序就能还原树形遍历顺序，再套窗口函数就安全了。

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level, CAST(id AS TEXT) AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.level + 1, o.path || '.' || e.id
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT 
  id,
  name,
  level,
  ROW_NUMBER() OVER (ORDER BY path) AS tree_rank,
  RANK() OVER (PARTITION BY level ORDER BY name) AS level_rank
FROM org;

需要动态重排时，避免在递归内计算累计值

比如你想按销售额累加生成“销售里程碑排名”，不要试图在递归里用 SUM() OVER (ORDER BY ...) —— 这会触发窗口函数禁令。正确做法是：递归只生成原始行 + 序号（seq），外层用 SUM() OVER (ORDER BY seq) 累计，再基于累计值做条件排名。

递归体中用 ROW_NUMBER() OVER (ORDER BY ...) 是非法的，但用 seq + 1 给下一层赋值是允许的
如果原始表没天然顺序字段，先用非递归 CTE 加 ROW_NUMBER() 生成序号，再作为递归基础
MySQL 用户注意：WITH RECURSIVE 从 8.0.1 开始支持，且最大递归深度默认 1000，超限会报 ERROR 3636，需设 cte_max_recursion_depth

PostgreSQL 数组路径比字符串更可靠

用字符串拼接 path（如 '1.12.123'）在排序时可能出错：'10' 会排在 '2' 前面。PostgreSQL 支持 ARRAY 类型，天然按元素逐位比较，更适合作为排序键。

WITH RECURSIVE tree AS (
  SELECT id, parent_id, ARRAY[id] AS path
  FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, t.path || n.id
  FROM nodes n
  JOIN tree t ON n.parent_id = t.id
)
SELECT 
  id,
  ROW_NUMBER() OVER (ORDER BY path) AS rank_by_tree
FROM tree;

这里 path 是 INTEGER[]，ORDER BY path 严格按树的深度优先顺序排列，不会因数字位数错乱。这是容易被忽略但影响结果正确性的细节。