Create Table Test(colunmnid Integer,precolumnid Integer)
Insert Into Test Values(0,Null);
Insert Into Test Values(2,0);
Insert Into Test Values(3,0);
Insert Into Test Values(4,2);
Insert Into Test Values(6,2);
Insert Into Test Values(7,3);
Insert Into Test Values(8,3);
Insert Into Test Values(9,4);
如图所示的树型图。
我要一条sql语句实现结果,不用游标,不用函数,
9 0
9 2
9 4
9 9
4 0
4 2
4 4
2 0
2 2
6 0
6 2
6 6
7 0
7 3
7 7
8 0
8 3
8 0
Insert Into Test Values(0,Null);
Insert Into Test Values(2,0);
Insert Into Test Values(3,0);
Insert Into Test Values(4,2);
Insert Into Test Values(6,2);
Insert Into Test Values(7,3);
Insert Into Test Values(8,3);
Insert Into Test Values(9,4);
如图所示的树型图。
我要一条sql语句实现结果,不用游标,不用函数,
9 0
9 2
9 4
9 9
4 0
4 2
4 4
2 0
2 2
6 0
6 2
6 6
7 0
7 3
7 7
8 0
8 3
8 0
from test a
WHERE precolumnid IS NOT null
connect by prior colunmnid= precolumnid
order by 1
select colunmnid,connect_by_root(colunmnid) precolumnid
from test
WHERE precolumnid IS NOT null
connect by prior colunmnid= precolumnid
order by 1,2 --result:
COLUNMNID PRECOLUMNID
---------------------------
2 0
2 2
3 0
3 3
4 0
4 2
4 4
6 0
6 2
6 6
7 0
7 3
7 7
8 0
8 3
8 8
9 0
9 2
9 4
9 9