有如下数据
CREATE TABLE a
(
aid NUMBER,
aname VARCHAR2(20),
paid NUMBER
)

INSERT INTO a VALUES(1,'中国',NULL);
INSERT INTO a VALUES(2,'北京',1);
INSERT INTO a VALUES(3,'海淀',2);
INSERT INTO a VALUES(4,'1局',3);
INSERT INTO a VALUES(5,'东城',1);
INSERT INTO a VALUES(6,'1局',5);
COMMIT;

想得到下面这样格式的数，请问应该怎么写SQL，谢谢了。
col1 col2 col3 col4
中国北京海淀 1局
中国北京东城 1局

正解：from ID: newkid

SELECT REGEXP_SUBSTR(p,'[^,]+',1,1) AS col1
,REGEXP_SUBSTR(p,'[^,]+',1,2) AS col2
,REGEXP_SUBSTR(p,'[^,]+',1,3) AS col3
,REGEXP_SUBSTR(p,'[^,]+',1,4) AS col4
FROM (
SELECT SYS_CONNECT_BY_PATH(aname,',') p
FROM a
WHERE CONNECT_BY_ISLEAF=1
START WITH paid IS NULL
CONNECT BY paid = PRIOR aid
);

知识点：

1. START WITH ... CONNECT BY ;

2.无论正树还是倒树, 关键就在于connect by的条件.
正树: 必须是 ‘父’= prior ‘子’
倒树: 必须是 ‘子’= prior ‘父;

3.CONNECT_BY_ISLEAF=1;

4.REGEXP_SUBSTR() 的使用。

[@more@]