单行不同列中的Oracle树结构层次结构数据显示
在oracle中，我有一个名为Employee_Hierarchy的层次表，名称为entity_code ， parent_entity_code ，entity_name和entity_role，没有循环。 在名为Client的其他表中较低位的子节点与具有entity_code的分层表最低位子节点连接。 我必须在单行层次结构中显示数据，其中列名将按角色添加。
示例示例：
树结构：
分层表：
降低大多数孩子的表格：
预期结果：
有没有办法通过oracle查询获得预期的结果？ 并且预期的结果取决于输入，这意味着它不会始终从根元素开始，它可以从任何节点开始，例如团队领导（Shail）到最低的孩子。
（ 注意 ：如果上面的层次缺失，那么当前节点的parent_code将是其上级层次的parent_code，并且缺少的层次元素在预期结果中将为空。）
提前致谢。
select h.Manager_entity_code
,h.Manager_entity_name
,h.Team_Lead_entity_code
,h.Team_Lead_entity_name
,h.Developer_entity_code
,h.Developer_entity_name
,c.client_name
from (select trim (both ',' from sys_connect_by_path (case when entity_role = 'Manager' then entity_code end,',')) as Manager_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Manager' then entity_name end,',')) as Manager_entity_name
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Team-Lead' then entity_code end,',')) as Team_Lead_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Team-Lead' then entity_name end,',')) as Team_Lead_entity_name
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Developer' then entity_code end,',')) as Developer_entity_code
,trim (both ',' from sys_connect_by_path (case when entity_role = 'Developer' then entity_name end,',')) as Developer_entity_name
from hierarchical_table
where connect_by_isleaf = 1
connect by parent_entity_code = prior entity_code
start with entity_code = 100
) h
join client_table c
on c.entity_code =
h.Developer_entity_code
order by h.Manager_entity_code
,h.Team_Lead_entity_code
,h.Developer_entity_code
;
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| MANAGER_ENTITY_CODE | MANAGER_ENTITY_NAME | TEAM_LEAD_ENTITY_CODE | TEAM_LEAD_ENTITY_NAME | DEVELOPER_ENTITY_CODE | DEVELOPER_ENTITY_NAME | CLIENT_NAME |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| 100 | Mack | 200 | Shail | 500 | Neha | Tata |
| 100 | Mack | 300 | Jack | 600 | Rocky | Rel |
| 100 | Mack | 300 | Jack | 600 | Rocky | Voda |
| 100 | Mack | 300 | Jack | 600 | Rocky | Airtel |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
start with entity_code = 300
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| MANAGER_ENTITY_CODE | MANAGER_ENTITY_NAME | TEAM_LEAD_ENTITY_CODE | TEAM_LEAD_ENTITY_NAME | DEVELOPER_ENTITY_CODE | DEVELOPER_ENTITY_NAME | CLIENT_NAME |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
| (null) | (null) | 300 | Jack | 600 | Rocky | Airtel |
| (null) | (null) | 300 | Jack | 600 | Rocky | Voda |
| (null) | (null) | 300 | Jack | 600 | Rocky | Rel |
+---------------------+---------------------+-----------------------+-----------------------+-----------------------+-----------------------+-------------+
在您的查询结果中，您希望某些角色在层次结构中具有特定的位置。 经理是第一层，队长是第二，开发者是第三。 所以你可以把你的分层表看作不是。 这使查询非常可读：
with manager as (select * from employee_hierarchy where entity_role = 'Manager')
, teamleader as (select * from employee_hierarchy where entity_role = 'Team-Lead')
, developer as (select * from employee_hierarchy where entity_role = 'Developer')
select
m.entity_code as manager_entity_code,
m.entity_name as manager_entity_name,
t.entity_code as team_lead_entity_code,
t.entity_name as team_lead__entity_name,
d.entity_code as developer_entity_code,
d.entity_name as developer_entity_name,
c.client_name
from manager m
join teamleader t on t.parent_entity_code = m.entity_code
join developer d on d.parent_entity_code = t.entity_code
left join client_table c on c.entity_code = d.entity_code;
如果您想将结果限制在团队负责人Shail的部门，只需添加相应的WHERE子句：
where t.entity_name = 'Shail'
这是使用连接来完成此操作的一种方法。 另一种方法是组合这两个表并进行分层查询 - 但这是相同的事情（分层查询不过是递归自连接）。
with
hierarchical_table ( entity_code, entity_name, entity_role, parent_entity_code ) as (
select 100, 'Mack' , 'Manager', cast (null as number) from dual union all
select 200, 'Shail', 'Team-Lead', 100 from dual union all
select 300, 'Jack' , 'Team-Lead', 100 from dual union all
select 400, 'Teju' , 'Developer', 200 from dual union all
select 500, 'Neha' , 'Developer', 200 from dual union all
select 600, 'Rocky', 'Developer', 300 from dual
),
client_table ( entity_code, client_name, address ) as (
select 600, 'Voda' , 'Pune' from dual union all
select 600, 'Rel' , 'Mumbai' from dual union all
select 600, 'Airtel', 'Pune' from dual union all
select 500, 'Tata' , 'Mumbai' from dual
)
-- end of test data (not part of the solution)
-- SQL query begins BELOW THIS LINE; use your actual table names
select h1.entity_code as manager_code, h1.entity_name as manager_name,
h2.entity_code as teamlead_code, h2.entity_name as teamlead_name,
h3.entity_code as developer_code, h3.entity_name as developer_name,
c.client_name
from hierarchical_table h1 left join hierarchical_table h2
on h2.parent_entity_code = h1.entity_code
left join hierarchical_table h3
on h3.parent_entity_code = h2.entity_code
left join client_table c
on c.entity_code = h3.entity_code
where h1.parent_entity_code is null
order by manager_code, teamlead_code, developer_code, client_name
;
输出 ：
MANAGER_CODE MANAGER_NAME TEAMLEAD_CODE TEAMLEAD_NAME DEVELOPER_CODE DEVELOPER_NAME CLIENT
------------ ------------ ------------- ------------- -------------- -------------- ------
100 Mack 200 Shail 400 Teju
100 Mack 200 Shail 500 Neha Tata
100 Mack 300 Jack 600 Rocky Airtel
100 Mack 300 Jack 600 Rocky Rel
100 Mack 300 Jack 600 Rocky Voda
5 rows selected.
上一篇: Oracle Tree Structure Hierarchy Data Display in Single Row Different Column