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 LEAF_FLAG
from tree_table1
where LVL4_NO is not null
o