代码如下,如果有问题详细说明!
SQL> select * from t_conn;
CID PID
---- ----
root 0
n root
n1 n
n2 n
m root
m1 m
m2 m
m3 m
x root
x1 x
x2 x
11 rows selected
SQL>
SQL>
SQL> select cid "子节点",
2 pid "父节点",
3 ln "层级"
4 from
5 (select ln,
6 flag,
7 rn,
8 sum(case when cid in('x1', 'x2', 'n1') then 1 else 0 end) over(partition by rn) cnt,
9 cid,
10 pid
11 from
12 (select ln,
13 flag,
14 max(rn) over(order by rn1) rn,
15 cid,
16 pid
17 from
18 (select level ln,
19 connect_by_isleaf flag,
20 decode(connect_by_isleaf, 0, row_number() over(order by rownum), 0) rn,
21 row_number() over(order by rownum) rn1,
22 cid,
23 pid
24 from t_conn
25 start with pid = '0'
26 connect by prior cid = pid)))
27 where (cnt = 0 and ln = 1) or (cnt <> 0 and flag <>1) or (cnt <> 0 and flag = 1 and cid in('x1', 'x2', 'n1'))
28 order by rn, flag, cid;
子节点 父节点 层级
------ ------ ----------
root 0 1
n root 2
n1 n 3
x root 2
x1 x 3
x2 x 3
6 rows selected
SQL>