我们正在使用Oracle 11g R1。这是代码：
CREATE TABLE T1 (ID NUMBER, PARENT_ID NUMBER, LEFT_SIBLING_ID NUMBER);
INSERT INTO T1 VALUES (1,NULL,NULL);
INSERT INTO T1 VALUES (3,1,NULL);
INSERT INTO T1 VALUES (2,1,3);
INSERT INTO T1 VALUES (4,2,NULL);
INSERT INTO T1 VALUES (5,2,4);
INSERT INTO T1 VALUES (10,NULL,1);
INSERT INTO T1 VALUES (12,10,NULL);
INSERT INTO T1 VALUES (11,10,12);
我希望结果是：
ID-Tree
1
3
2
4
5
10
12
11
这里的关键是，除了通常的PRIOR ID = PARENT_ID层次结构之外，还有另一个基于PRIOR ID = LEFT_SIBLING_ID的层次结构。子级按PRIOR ID = LEFT_SIBLING_ID的顺序排序。这就是3后面跟2，12后面跟11等的原因。这个顺序很重要。
我已经为如何做到这一点画了一个空白。
编辑：
更多的行可以清楚地说明订购问题：
CREATE TABLE T1 (ID NUMBER, PARENT_ID NUMBER, LEFT_SIBLING_ID NUMBER);
INSERT INTO T1 VALUES (1,NULL,10);
INSERT INTO T1 VALUES (3,1,NULL);
INSERT INTO T1 VALUES (2,1,3);
INSERT INTO T1 VALUES (4,2,NULL);
INSERT INTO T1 VALUES (5,2,4);
INSERT INTO T1 VALUES (10,NULL,NULL);
INSERT INTO T1 VALUES (12,10,NULL);
INSERT INTO T1 VALUES (7,10,12);
INSERT INTO T1 VALUES (11,10,7);
INSERT INTO T1 VALUES (6,1,2);
INSERT INTO T1 VALUES (13,1,6);
COMMIT;
查询结果：
select substr('----------', 1, lvl*2-2) || to_char(id) id_tree
from
(select SYS_CONNECT_BY_PATH(to_char(id,'009'), ':') sort_path,
left_sibling_id, id, parent_id, level lvl
from t1
start with parent_id is null
connect by prior id = parent_id) q
start with left_sibling_id is null
connect by prior id = left_sibling_id
and coalesce(parent_id,id) = coalesce(parent_id,id)
order by case lvl when 1 then sort_path
else substr(sort_path,1,length(sort_path)-4) end,
level;
ID_TREE
--------------------------------------------------
1
--3
--2
--6
--13
----4
----5
10
--12
--7
--11
11 rows selected
尽管兄弟姐妹的排序正确（顶层除外），但它们不再紧靠其父辈。
最佳答案
最后！
通过在LEFT_SIBLING_ID上执行CONNECT BY，我在下面创建了一个名为“ SIBLING_LEVEL”的“排序顺序”列。然后，我将其加入了原始表。然后，根据该连接的结果，进行了简单的CONNECT BY ORDER SIBLING BY。对我来说似乎有点蛮力，但这就是我能想到的。
SELECT ROWNUM, LPAD(' ', (LEVEL*2) - 1, '-') || ID AS HIERARCHY, PARENT_ID,
LEFT_SIBLING_ID, LEVEL AS PARENT_CHILD_LEVEL
from
(
SELECT A.ID, A.PARENT_ID, A.LEFT_SIBLING_ID, B.SIBLING_LEVEL
FROM
T1 A
,
(
SELECT ID, SUBSTR('----------', 1, LVL*2-2) || TO_CHAR(ID) ID_TREE,
LEVEL AS SIBLING_LEVEL
FROM
(SELECT SYS_CONNECT_BY_PATH(TO_CHAR(ID,'009'), ':') SORT_PATH,
LEFT_SIBLING_ID, ID, PARENT_ID, LEVEL LVL
FROM T1
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ID = PARENT_ID) Q
START WITH LEFT_SIBLING_ID IS NULL
CONNECT BY PRIOR ID = LEFT_SIBLING_ID) B
WHERE A.ID = B.ID
) C
START WITH C.PARENT_ID IS NULL
CONNECT BY PRIOR C.ID = C.PARENT_ID
ORDER SIBLINGS BY SIBLING_LEVEL;
http://sqlfiddle.com/#!4/fcd68/5/0