如何利用SQL获取叶节点的根节点信息

Question

假设我有一个具有以下结构的表：

---------------------------------------------------
TREE_GID | NODE_ID | PARENT_NODE_ID | TREE_NAME   |
---------------------------------------------------
1        | 1       |                | A           |
1        | 2       | 1              |             |
1        | 3       | 1              |             |
1        | 4       | 2              |             |
1        | 5       | 4              |             |
1        | 6       |                | B           |
1        | 7       | 6              |             |
2        | 1       |                | C           |
2        | 2       | 1              |             |
---------------------------------------------------

注意：

• 数据模型是多层次的树.
• TREE_GID是一组树。它可以有超过一棵树。
• NODE_ID是每个TREE_GID中的唯一数字。
• TREE_NAME仅显示在根节点中。

我想在每个节点中显示TREE_NAME。产出应类似于：

---------------------------------
TREE_ID | NODE_ID | TREE_NAME   |
---------------------------------
1       | 1       | A           |
1       | 2       | A           |
1       | 3       | A           |
1       | 4       | A           |
1       | 5       | A           |
1       | 6       | B           |
1       | 7       | B           |
2       | 1       | C           |
2       | 2       | C           |
---------------------------------

我怎么弄到它们？

Answer 1

递归的通用表表达式将以相当直接的方式完成；

WITH cte(tree_gid, node_id, tree_name) AS (
  SELECT tree_gid, node_id, tree_name FROM mytable WHERE tree_name IS NOT NULL
  UNION ALL
  SELECT m.tree_gid, m.node_id, cte.tree_name
  FROM mytable m 
  JOIN cte ON cte.node_id = m.parent_node_id AND cte.tree_gid = m.tree_gid 
)
SELECT * FROM cte
ORDER BY tree_gid, node_id;

编辑:您也可以使用CONNECT BY作为您的评论链接描述；

SELECT tree_gid, node_id, CONNECT_BY_ROOT tree_name
  FROM mytable
  START WITH tree_name IS NOT NULL
  CONNECT BY PRIOR node_id = parent_node_id AND PRIOR tree_gid = tree_gid
  ORDER SIBLINGS BY tree_gid, node_id;

测试两者的SQLfiddle。