oracle层级关系按列存储_oracle递归实现查找整个层级关系

CREATE TABLE TEST_A(ID NUMBER,CSNAME VARCHAR2(200),PARENTID NUMBER);

INSERT INTO TEST_A(ID ,CSNAME ,PARENTID)

VALUES(1,'四川省',NULL);

INSERT INTO TEST_A(ID ,CSNAME ,PARENTID)

VALUES(10,'成都市',1);

INSERT INTO TEST_A(ID ,CSNAME ,PARENTID)

VALUES(11,'绵阳市',1);

INSERT INTO TEST_A(ID ,CSNAME ,PARENTID)

VALUES(100,'金牛区',10);

COMMIT;

WITH TEST_B AS (SELECT ID,RPAD(ID,3) || ' ' || CSNAME CSN,PARENTID,LEVEL LVL

FROM TEST_A A

CONNECT BY PRIOR A.ID = A.PARENTID

START WITH A.PARENTID IS NULL

)

SELECT CASE WHEN C.LVL = 1 THEN A.CSN

WHEN C.LVL = 2 THEN A.CSN || ' ' || C.CSN

ELSE A.CSN || ' ' || B.CSN || ' ' || C.CSN

END

FROM TEST_B A

LEFT JOIN TEST_B B

ON A.ID = B.PARENTID

LEFT JOIN TEST_B C

ON B.ID = C.PARENTID

WHERE A.PARENTID IS NULL;