具有多个节点的 Oracle 分层查询

看看你的数据结构。您有一行代表 DMID 1、PMID 1，您希望在树中将其显示为两个单独的行。不知何故，您需要在结果集中创建额外的行来表示这些中间节点。一个很好的方法是使用GROUPING SETS.

所以，我们要做的是连接这两个表，并GROUP BY GROUPING SETS(...)以这种方式获得所有节点的合并层次结构，无论它们是 DM、PM 还是 DEV 节点。

一旦我们有了它，我们将只CONNECT BY对合并的层次结构进行简单的查询。

我在下面粘贴了该计划的一个有效实现，但首先让我说，如果您需要经常做这样的事情，请认真考虑可能您的数据模型设计不正确。

这是带有注释的查询。此版本将为您提供帖子右侧的树：即省略TL级别的树。一个简单的修改会让左边的树变成......不清楚你真正想要的是哪一个（抱歉）。

-- First provide data to simulate your V_Mgmt table...
With V_Mgmt ( DMid, PMId, TLId ) AS (
SELECT 1,       1,       1 FROM DUAL UNION ALL
SELECT 1,       1,       2 FROM DUAL UNION ALL
SELECT 1,       2,       3 FROM DUAL UNION ALL
SELECT 2,       3,       4 FROM DUAL UNION ALL
SELECT 2,       3,       5 FROM DUAL UNION ALL
SELECT 2,       4,       6 FROM DUAL ),
-- ... and your T_ProjLevels table
T_ProjLevels (TLId,    DevId,  ParentDevId) AS (
SELECT 1,       1,       0 FROM DUAL UNION ALL
SELECT 1,       2,       1 FROM DUAL UNION ALL
SELECT 1,       3,       1 FROM DUAL UNION ALL
SELECT 2,       4,       0 FROM DUAL UNION ALL
SELECT 2,       5,       4 FROM DUAL UNION ALL
SELECT 2,       6,       4 FROM DUAL UNION ALL
SELECT 2,       7,       6 FROM DUAL UNION ALL
SELECT 3,       8,       0 FROM DUAL UNION ALL
SELECT 3,       9,       0 FROM DUAL UNION ALL
SELECT 4,       10,      0 FROM DUAL UNION ALL
SELECT 4,       11,      0 FROM DUAL UNION ALL
SELECT 4,       12,     11 FROM DUAL ),
-- Next, merge them together 
--  (A) using GROUPING_SETS to create extra rows for the DM, PM, but not the TL-level nodes
--  (B) combining DM, PM, (but not TL), and DEV ids into a common set of "node", "parent_node", and "node_name" columns
merged_hierarchy ( node, parent_node, node_name ) AS (
SELECT rtrim(m.dmid || '.' || m.pmid || '.' || pl.devid,'.') node, 
rtrim(
case 
when grouping(m.pmid) = 1 then NULL
when grouping(m.tlid) = 1 then to_char(m.dmid)
when grouping(pl.devid) = 1 then m.dmid || '.' || m.pmid
else
m.dmid || '.' || m.pmid || '.' || nullif(pl.parentdevid,0) end,'.') parent_node,
case when grouping(pl.devid) = 0 THEN 'DEV' || pl.devid
when grouping(m.tlid) = 0 THEN 'TL' || m.tlid
when grouping(m.pmid) = 0 THEN 'PM' || m.pmid
when grouping(m.dmid) = 0 THEN 'DM' || m.dmid
end node_name
from v_mgmt m 
left join t_projlevels pl on pl.tlid = m.tlid
group by grouping sets ( ( m.dmid ), ( m.dmid, m.pmid), (m.dmid, m.pmid, m.tlid, pl.devid, pl.parentdevid ) )
)
-- Finally, query the merged hierarchy as a straight-forward CONNECT BY query
SELECT lpad(' ',5*(level-1),' ') || node_name output
FROM merged_hierarchy
START WITH parent_node IS NULL
CONNECT BY parent_node = prior node 
-- Exclude the outer-joined rows from T_ProjLevels...
AND node_name != 'DEV';

+--------------------------+
|          OUTPUT          |
+--------------------------+
| DM1                      |
|      PM1                 |
|           DEV1           |
|                DEV2      |
|                DEV3      |
|           DEV4           |
|                DEV5      |
|                DEV6      |
|                     DEV7 |
|      PM2                 |
|           DEV8           |
|           DEV9           |
| DM2                      |
|      PM3                 |
|           DEV10          |
|           DEV11          |
|                DEV12     |
|      PM4                 |
+--------------------------+