当给定记录的级别是随机的时，选择最高关联

Question

涉及三个表。 DBMS是Oracle 10g。

员工 = 单个员工记录

• Emp_id （PK）
• Emp_name
• 各种详细信息字段

部门 = 包含分层组织结构

• Dept_code（PK）
• 部门名称
• Parent_id（指上级部门同一表中的dept_code）
• depth_level（1=最高级别，2=1的子部门，依此类推... 最大值 = 6）
• 各种详细信息字段

关联 = 将员工映射到部门

• Assoc_id （包）
• emp_id （FK）
• dept_code （FK）
关联
• 不同关联类型的其他字段

在关联将员工映射到不同深度的部门的地方，我想运行一个查询，该查询计算按深度 = 2 分组的所有员工。 如果员工在 6 级部门工作，我需要解决 5 级，然后是 4 级，然后是 3 级才能达到 2 级，但如果他们在 3 级部门工作，我只需要解析到 2 级。

试图找出最有效的方法。 到目前为止，我正在考虑运行 5 个单独的查询，每个深度一个，具有不同数量的子查询来解决深度级别，然后与联合结合使用。 我的第二个想法是创建一个静态参考表，将每个部门代码映射到 2 级标签，但维护该表会有问题。

有人有更好的主意吗？

Answer 1

递归 CTE 挽救了这一天。 如果我的问题不清楚，我深表歉意，这是我的解决方案，尽管我可能已经更改了原始帖子中的一些字段名称。 我计划将联合查询第一部分中 U.ID 的静态值替换为一个参数，该参数将任何部门编码并检索其各自的下属部门。

在这种情况下，部门代码"5000002"是IT部门，结果显示IT部门层次结构各个级别的所有员工。

select r.full_name, r.id, u.dept_name, u.dept_id, u.dept_level
from clarity.srm_resources r,
clarity.PRJ_OBS_ASSOCIATIONS a,
(with DIRECT_DEPT (Parent_ID, Dept_ID, Dept_Name, Dept_Level)
as
(
SELECT U.PARENT_ID, U.ID AS DEPT_ID, U.NAME AS DEPT_NAME, 0 AS Dept_Level
FROM  clarity.prj_obs_units u
where u.type_id = '5000001'
AND U.ID = '5000002'
UNION ALL
SELECT U.PARENT_ID, U.ID AS DEPT_ID, U.NAME AS DEPT_NAME, Dept_Level +1
FROM  clarity.prj_obs_units u
INNER JOIN DIRECT_DEPT D
ON U.PARENT_ID = D.DEPT_ID
where u.type_id = '5000001'
)
SELECT Parent_ID, Dept_ID, Dept_Name, Dept_Level
FROM DIRECT_DEPT) u
where a.record_id = r.id
and a.unit_id = u.dept_id
and a.table_name ='SRM_RESOURCES'
and r.is_active = '1'
;