不能直接用 ROW_NUMBER() 给树节点编号，因为窗口函数不感知树的遍历顺序，仅按字段全局排序，导致层级错乱；必须先用递归 CTE 构建 path 字段（如 '1/5/23'），再基于路径做排序或计算。
为什么不能直接用 ROW_NUMBER() 给树节点编号
很多人一上来就用 ROW_NUMBER() OVER (ORDER BY id) 给树节点强行排序，结果发现层级错乱、父子顺序颠倒。根本原因是：窗口函数默认不感知树的遍历顺序，它只按指定字段全局排序，而树形结构需要深度优先（DFS）或广度优先（BFS）的路径感知能力。
真正能支撑树形重构的是带递归 CTE 配合窗口函数的组合——先用 WITH RECURSIVE 展开路径，再用窗口函数在已展开的路径上做层级计算。
-
ROW_NUMBER()和RANK()在树场景下几乎无用，除非你已经生成了合法的遍历序列 - 必须先通过递归 CTE 构建
path字段（如'1/5/23'），这是后续所有层级计算的基础 - PostgreSQL / SQL Server / DuckDB 支持递归 CTE；MySQL 8.0+ 也支持，但早期版本需用自连接模拟，性能极差
如何用 STRING_AGG() + 递归 CTE 构建可排序的路径
核心不是“算层级”，而是“构造能反映层级关系的字符串路径”。不同数据库对路径拼接函数名不同，但逻辑一致：父路径 + 当前ID，用固定分隔符连接。
以 PostgreSQL 为例：
WITH RECURSIVE tree_path AS (
SELECT id, parent_id, name, 1 AS level, CAST(id AS TEXT) AS path
FROM nodes WHERE parent_id IS NULL
UNION ALL
SELECT n.id, n.parent_id, n.name, tp.level + 1,
tp.path || '/' || n.id
FROM nodes n
JOIN tree_path tp ON n.parent_id = tp.id
)
SELECT *,
STRPOS(path, '/') AS first_slash_pos,
ARRAY_LENGTH(STRING_TO_ARRAY(path, '/'), 1) AS depth
FROM tree_path
ORDER BY path;-
STRING_TO_ARRAY(path, '/')是 PostgreSQL 获取层级深度最稳的方式；MySQL 用CHAR_LENGTH(path) - CHAR_LENGTH(REPLACE(path, '/', '')) + 1 - 不要用
level字段做最终排序依据——它只表示递归层数，不能保证同层兄弟顺序（比如插入顺序乱时） -
ORDER BY path才是真正的 DFS 顺序，因为字符串比较天然符合字典序遍历
COUNT(*) OVER (PARTITION BY ...) 能否统计子树节点数
不能直接用。窗口函数的 PARTITION BY 只能按现有行分组，而子树包含后代所有行，属于“向下聚合”，必须依赖递归展开后的路径特征来定义分组边界。
正确做法是利用路径前缀匹配：
WITH RECURSIVE tree_path AS (
SELECT id, parent_id, name, CAST(id AS TEXT) AS path
FROM nodes WHERE parent_id IS NULL
UNION ALL
SELECT n.id, n.parent_id, n.name, tp.path || '/' || n.id
FROM nodes n
JOIN tree_path tp ON n.parent_id = tp.id
),
subtree_count AS (
SELECT tp1.*,
(SELECT COUNT(*)
FROM tree_path tp2
WHERE tp2.path LIKE tp1.path || '%') AS subtree_size
FROM tree_path tp1
)
SELECT * FROM subtree_count;- 嵌套子查询比窗口函数更直观可靠；试图用
COUNT(*) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)会因排序不可控而失败 - 路径前缀匹配（
LIKE tp1.path || '%'）是关键，它把“子树”转化为字符串前缀问题 - 注意索引：给
path字段加 B-Tree 索引可显著加速LIKE '1/5/%'查询
Oracle 的 CONNECT BY 和标准窗口函数怎么协同
Oracle 是特例：它原生支持 CONNECT BY 做树遍历，但它的伪列（如 LEVEL, CONNECT_BY_ISLEAF）不能直接参与窗口函数的 OVER 子句——因为 CONNECT BY 不是标准 SQL，解析器不将其输出视为常规结果集。
必须两步走：先用 CONNECT BY 生成带路径的结果，再用外层查询套窗口函数：
SELECT id, parent_id, name, lvl, path,
COUNT(*) OVER (PARTITION BY SUBSTR(path, 1, INSTR(path, '/', 1, lvl-1)-1)) AS siblings_count
FROM (
SELECT id, parent_id, name, LEVEL AS lvl,
SYS_CONNECT_BY_PATH(id, '/') AS path
FROM nodes
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
);- 内层
CONNECT BY输出必须显式别名（如lvl,path），否则外层无法引用 - Oracle 的
SYS_CONNECT_BY_PATH不支持自定义分隔符长度，且对空值敏感，建议提前NVL(parent_id, 0)处理 - 不要在
CONNECT BY内写OVER——语法报错，ORA-30483
实际应用中，最易被忽略的是路径字符串的编码安全。如果节点 ID 含斜杠、反斜杠或 Unicode 分隔符（比如中文顿号），path 字段会失效。生产环境务必统一使用 URL-safe 编码或 UUID 作为节点标识。
