第一步：找到树的根

给定您的输入表，您可以通过选择

• 具有“PATH_LENGTH=0”的所有祖先(只选择一次)
• 在具有“PATH_LENGTH>0”的子代中找不到的节点(那些至少从Level=1开始找到的节点继续)。

SELECT ancestor AS root FROM tab WHERE path_length = 0
EXCEPT
SELECT descendant FROM tab WHERE path_length > 0

根目录

1
2
3

第二步：实现深度优先搜索您的二叉树。

这可以通过以下方式完成

• 通过连接上一个表，扫描值为“ASSENTOR”的行只属于根表(以避免重复的“DETENDANT”值)
• 应用深度优先排序。

深度优先排序将基于以下因素进行排序：

• 先辈至上
• 使用来自ROW_NUMBER窗口函数的排名值，以递归(深度优先)方式显示每个子代的第一个子代，
• 使用CASE构造来处理这两种情况时，路径长度在向叶子深处移动时向上移动，在向根部移动时相同的路径_长度向下移动。

WITH roots AS (
    SELECT ancestor AS root FROM tab WHERE path_length = 0
    EXCEPT
    SELECT descendant FROM tab WHERE path_length > 0
), ranked_nodes AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ancestor, path_length
                                ORDER     BY descendant           ) AS rn
    FROM tab
    INNER JOIN roots
            ON tab.ancestor = roots.root
)
SELECT descendant
FROM ranked_nodes
ORDER BY ancestor, 
         rn, 
         CASE WHEN rn = 1 THEN path_length ELSE -path_length END

查看演示here。
上面的解决方案是一个通用的解决方案，但如果您假设事先知道根(1、2和3)的值，则可以按如下方式简化查询：

WITH ranked_nodes AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ancestor, path_length
                                ORDER     BY descendant           ) AS rn
    FROM tab
    WHERE ancestor <= 3
)
SELECT descendant
FROM ranked_nodes
ORDER BY ancestor, 
         rn, 
         CASE WHEN rn = 1 THEN path_length ELSE -path_length END