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

Question

一共有三张桌子。数据库管理系统是Oracle 10g。

雇员=个别雇员纪录

• Emp_id (PK)
• Emp_name
• 各种细节字段

Department =包含分层组织结构

• Dept_code (PK)
• 部门名称
• Parent_id (指父部门在同一个表中的dept_code )
• depth_level (1=highest级，2= 1的子部等)(最大值= 6)
• 各种详细信息字段

协会=将雇员映射到部门

• Assoc_id (pk)
• emp_id (fk)
• dept_code (fk)
• 与不同关联类型相关的其他字段

当关联将员工映射到不同深度的部门时，我希望运行一个查询，将所有在dept = 2处分组的员工进行计数。如果员工在级别6的部门工作，我需要解析级别5，然后解析级别4，然后解析到级别3，但如果他们在级别3的部门工作，我只需要解析到级别2。

想找出最有效的方法。到目前为止，我正在考虑运行5个单独的查询，每个深度一个，每个深度都有不同数量的子查询来解析深度级别，然后与union结合。我的第二个想法是创建一个静态引用表，将每个部门代码映射到第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'
;