有如下结构:x y y_parent
1 11 1
1 12 1
2 21 2
2 22 2
3 31 3
3 311 31
3 3111 311
4 41 4
4 411 41
5 51 5
5 52 5查询出所有的节点关系,分层显示。
1 11 1
1 12 1
2 21 2
2 22 2
3 31 3
3 311 31
3 3111 311
4 41 4
4 411 41
5 51 5
5 52 5查询出所有的节点关系,分层显示。
--兵哥是这么做的:
SELECT lpad(y, LEVEL + length(y), '-')
FROM (SELECT *
FROM t
UNION ALL
SELECT DISTINCT NULL, y_parent, NULL
FROM t
WHERE length(to_char(y_parent)) = 1)
START WITH y_parent IS NULL
CONNECT BY PRIOR y = y_parent;
SQL> desc t;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
X NUMBER Y
Y VARCHAR2(10) Y
Y_PARENT VARCHAR2(10) Y
SQL> select * from t;
X Y Y_PARENT
---------- ---------- ----------
1 11 1
1 12 1
2 21 2
2 22 2
3 31 3
3 311 31
3 3111 311
4 41 4
4 411 41
5 51 5
5 52 5
11 rows selected
SQL>
SQL> SELECT lpad(y, LEVEL + length(y), '-')
2 FROM (SELECT *
3 FROM t
4 UNION ALL
5 SELECT DISTINCT NULL, y_parent, NULL
6 FROM t
7 WHERE length(y_parent) = 1)
8 START WITH y_parent IS NULL
9 CONNECT BY PRIOR y = y_parent
10 ORDER BY y;
LPAD(Y,LEVEL+LENGTH(Y),'-')
--------------------------------------------------------------------------------
-1
--11
--12
-2
--21
--22
-3
--31
---311
----3111
-4
--41
---411
-5
--51
--52
16 rows selected
SQL>
FROM (SELECT pkg_id,child_id,child_parent_id
FROM z
UNION ALL
SELECT DISTINCT NULL, pkg_id, NULL FROM z)
START WITH child_parent_id IS NULL
CONNECT BY PRIOR child_id = child_parent_id
ORDER SIBLINGS BY child_id;