oracle的树结构查询（两种树表结构转化）

oracle的树结构查询（两种树表结构转化）

下图是一个树图，主要是为了直观的看到每个节点的关系

我会用两个不同的树表将这个图存在数据库中

下面是第一种树表（这一种很直观，是从根节点直接到叶子节点，与上图相匹配，这种树图逻辑清楚，但是出现了重复性的节点）

下面是第二种树表（这是以每一个节点为一行数据，用上级节点进行联系，避免了重复，但是节点关系没有上表清楚）

将第一种树表转化为第二种树表的SQL语句是

   select distinct LVL1_NO as ORG_ID,
                   'root' as UP_ORG,
                   LVL1_NAME as ORG_NAME,
                   1 as LVL,
                   0 as LEAF_FLAG
     from tree_table1 t1
   
   union all
   
   select distinct LVL2_NO as ORG_ID,
                   LVL1_NO as UP_ORG,
                   LVL2_NAME as ORG_NAME,
                   2 as LVL,
                   case
                     when LVL3_NO is null then
                      1
                     else
                      0
                   end as LEAF_FLAG
     from tree_table1
   
   union all
   
   select distinct LVL3_NO as ORG_ID,
                   LVL2_NO as UP_ORG,
                   LVL3_NAME as ORG_NAME,
                   3 as LVL,
                   case
                     when LVL4_NO is null then
                      1
                     else
                      0
                   end as LEAF_FLAG
     from tree_table1
   
   union all
   
   select distinct LVL4_NO   as ORG_ID,
                   LVL3_NO   as UP_ORG,
                   LVL4_NAME as ORG_NAME,
                   4         as LVL,
                   1         as