oracle层级聚合必须先用connect by展平树结构再分组，因group by仅统计直属员工；需用connect_by_root dept_id获取顶层部门id并参与select和group by，不可加别名；过滤应放外层避免剪枝；12c+推荐用with recursive cte替代，逻辑更清晰可控。
CONNECT BY 必须先展开再分组，不能直接 GROUP BY
Oracle 层级聚合的核心陷阱是：写 SELECT dept_id, SUM(salary) FROM ... GROUP BY dept_id 只会算出每个部门自己员工的工资和，完全不包含下属部门数据。因为原始表里 salary 是按员工物理归属记录的，不是按管理范围存储的。
真正要统计“某部门及其所有子部门的总工资”，必须先用 CONNECT BY 把整棵子树拉平成多行（每个叶子员工一行，附带其所在路径上的所有上级部门），再在外层按根节点分组。关键点是用 CONNECT_BY_ROOT dept_id 获取每个员工最终归属的顶层部门 ID。
- 内层查询必须返回每行对应的根部门 ID，不能只返回当前部门 ID
-
CONNECT_BY_ROOT必须同时出现在SELECT和GROUP BY中，且不能加别名后引用（比如不能写CONNECT_BY_ROOT dept_id AS root_id然后在GROUP BY root_id） - 如果在
CONNECT BY子句后直接加WHERE status = 'A'过滤员工，会提前剪枝——应改用CONNECT_BY_ISLEAF = 1或把过滤移到外层查询
用 WITH RECURSIVE 替代 CONNECT BY（Oracle 12c+）
Oracle 12c 起支持标准 SQL 的递归 CTE，语法更清晰、逻辑更可控，尤其适合需要中间状态或多次引用同一层级结果的场景。比如要统计每层部门的员工数、平均薪资、最大深度，用 CTE 分阶段写比嵌套 CONNECT BY 更易维护。
注意：CTE 递归必须有明确的终止条件（如 level ），否则可能死循环；Oracle 默认递归深度限制为 100，可通过 <code>/*+ NOCYCLE */ 提示避免 ORA-01436 错误，但不能代替逻辑终止条件。
- 递归 CTE 的锚成员（anchor）对应
START WITH，递归成员（recursive）对应CONNECT BY PRIOR -
LEVEL伪列需手动用1 AS lvl和lvl + 1模拟 - 若需路径字符串，
SYS_CONNECT_BY_PATH在 CTE 中不可用，得用字符串拼接模拟
动态 SQL 展平任意深度组织结构
当分管领导字段只存于顶层组织、下级组织该字段为 NULL，且树深度不确定时，硬编码 JOIN 多次表（t1→t2→t3…）不可维护。这时要用动态 SQL 构造语句：先查出目标组织的 LEVEL，再拼接对应层数的 LEFT JOIN 链。
例如查组织 “cc_1_1” 的分管领导，先得 SELECT LEVEL FROM depts START WITH id = 1 CONNECT BY PRIOR parent_id = id 得到深度为 3，再生成含 3 个 LEFT JOIN 的 SQL，让末级组织左连到第 1 级（顶层）取 mgr 字段。
- 动态 SQL 中必须用
EXECUTE IMMEDIATE执行拼接后的字符串，不能直接 SELECT - 拼接时注意表别名唯一性（t1/t2/t3…）和 ON 条件的层级对应（t2.parent_id = t1.id, t3.parent_id = t2.id）
- 深度过大时（如 >10），动态拼接性能下降明显，建议预计算并缓存到物化视图
子查询嵌套 vs CTE：什么时候该用哪个？
单纯展平一层树（如查某部门所有下属员工）用 CONNECT BY 最快；需要复用中间结果（比如先展平、再算各层人数、再算占比）就该用 CTE；若逻辑跨多个业务步骤（如先过滤活跃部门、再展平、再关联绩效表），CTE 分步命名能大幅降低理解成本。
子查询嵌套超过 3 层就容易出错——不是语法错，而是 WHERE 条件作用域混淆、别名覆盖、执行顺序难推断。CTE 每一块独立定义、可单独调试，比如 WITH flat AS (SELECT ... CONNECT BY), stat AS (SELECT COUNT(*) FROM flat GROUP BY root_dept_id)，一眼看清数据流。
- CTE 不提升性能，只是提升可读性和可维护性；实际执行计划和等价子查询基本一致
- Oracle 对 CTE 有优化机制（如自动物化），但仅限简单场景；复杂 CTE 建议加
MATERIALIZE提示强制缓存 - 别在 CTE 里写 DML；CTE 本质是查询临时命名，不是真实表
CONNECT_BY_ROOT、哪一层该用 CTE 递归、哪一层必须切出去用 PL/SQL 动态拼。层级越深，数据越稀疏，越容易在 CONNECT BY 的剪枝逻辑和外层过滤之间漏掉关键节点。
