使用Oracle层级查询获取表中父子孙节点并合并为单行

Got it, let's tackle this problem. You need to take each independent parent-child hierarchy chain from your table and collapse all its nodes into a single space-separated row. Oracle's built-in hierarchical query tools are perfect for this—here's how to make it work:

Step-by-Step Breakdown #

First, we need to:

1. Spot the root nodes (nodes with no parent, meaning their Col1 value never shows up in Col2 of any record).
2. Traverse each full hierarchy chain starting from these roots.
3. Capture the complete path of nodes for each chain and format it into a single string.

Final Query #

Assuming your source table is named your_table, this query will do exactly what you need:

SELECT 
    TRIM(SYS_CONNECT_BY_PATH(Col1, ' ') || ' ' || Col2) AS hierarchy_chain
FROM your_table
WHERE CONNECT_BY_ISLEAF = 1
START WITH Col1 NOT IN (SELECT Col2 FROM your_table)
CONNECT BY PRIOR Col2 = Col1;

What Each Part Does #

Let's break down the key components so you understand how it works:

• START WITH Col1 NOT IN (SELECT Col2 FROM your_table): This sets the starting point for each chain—only nodes that have no parent (they never appear as a child in Col2) are used as roots.
• CONNECT BY PRIOR Col2 = Col1: This defines the parent-child relationship: the child node (Col2) from the previous row becomes the parent node (Col1) of the next row, letting Oracle walk the entire hierarchy chain.
• CONNECT_BY_ISLEAF = 1: We only keep rows that are leaf nodes (the last node in each chain). This ensures we get one row per complete hierarchy instead of multiple rows for each step in the chain.
• SYS_CONNECT_BY_PATH(Col1, ' '): This builds a space-separated string of nodes from the root to the current parent node. We append ' ' || Col2 to add the leaf node, then TRIM() removes any leading space from the final string.

Sample Output #

For your provided data, this query will return:

Which matches exactly the expected output you described.

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