如何用Oracle CONNECT BY层级查询获取员工对应的全量部门层级数据

问题原因 #

你现有SQL出现重复的核心问题是：先将员工表和部门表关联后再执行层级遍历，每一行「员工+所属部门」的记录都会作为独立起点触发一次部门树遍历，最终生成大量重复链路。

正确实现思路 #

先单独完成部门层级的全量展开，生成「子部门-所有上级部门节点」的映射关系，再和员工表关联，就能得到每个员工对应的全链路层级记录：

SELECT
  e.id AS ID_E,
  d_hierarchy.root_parent AS ROOT,
  d_hierarchy.dept_id AS DEPT
FROM Employees e
INNER JOIN (
  -- 先展开所有部门的层级链路：从每个子部门向上遍历所有父节点
  SELECT
    CONNECT_BY_ROOT d.id AS child_dept_id,
    d.parent_id AS root_parent,
    d.id AS dept_id
  FROM Departments d
  CONNECT BY d.id = PRIOR d.parent_id
) d_hierarchy ON e.dept = d_hierarchy.child_dept_id
ORDER BY e.id, d_hierarchy.dept_id

效果说明 #

以上查询输出的逻辑完全匹配你的预期：

• 每个员工会根据所属部门的层级深度，生成对应条数的记录，比如所属部门是4（层级3：1→2→4）的员工，会生成3条记录，分别对应部门1、2、4
• ROOT字段为当前层级部门的父部门ID，最高级部门1的父ID为null，和你给出的示例输出完全一致

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