数据库有这样的三列:
no level topno
1 1 0
2 1 0
3 2 1
4 2 2
5 2 1
6 2 2
编号,级别,和上级编号。比如第一行是第三行的上级。
我要查一个LIST列表出来,排序的方式是这样的:
首先第一条记录按level排序,然后再按topbo排序,不好表达, 就看我想要的结果吧:
第一行应该是1 1 0
第二行应该是3 2 1
第三行应该是5 2 1
第四行应该是2 1 0
第五行应该是4 2 2
第六行应该是6 2 2order by ?
no level topno
1 1 0
2 1 0
3 2 1
4 2 2
5 2 1
6 2 2
编号,级别,和上级编号。比如第一行是第三行的上级。
我要查一个LIST列表出来,排序的方式是这样的:
首先第一条记录按level排序,然后再按topbo排序,不好表达, 就看我想要的结果吧:
第一行应该是1 1 0
第二行应该是3 2 1
第三行应该是5 2 1
第四行应该是2 1 0
第五行应该是4 2 2
第六行应该是6 2 2order by ?
NO LVL TOPNO
---------- ---------- ----------
1 1 0
2 1 0
3 2 1
4 2 2
5 2 1
6 2 2
6 rows selected
SQL>
SQL> select * from t_no t start with t.topno=0 connect by prior t.no=t.topno
2 order siblings by no
3 ;
NO LVL TOPNO
---------- ---------- ----------
1 1 0
3 2 1
5 2 1
2 1 0
4 2 2
6 2 2
6 rows selected
SQL>
select 1 no,1 wlevel,0 topno from dual union all
select 2, 1, 0 from dual union all
select 3, 2, 1 from dual union all
select 4, 2, 2 from dual union all
select 5, 2, 1 from dual union all
select 6, 2, 2 from dual)
--以上为提供数据的语句
select * from tb
connect by nocycle topno=prior no
start with topno=0 NO WLEVEL TOPNO
---------- ---------- ----------
1 1 0
3 2 1
5 2 1
2 1 0
4 2 2
6 2 2
这种单独order by是做不到 只能递归
with tb as
(select 1 no,1 leve,0 topno from dual union all
select 2,1 ,0 from dual union all
select 3 ,2, 1 from dual union all
select 4 ,2, 2 from dual union all
select 5 ,2, 1 from dual union all
select 6 ,2, 2 from dual )
select no,leve,topno from tb
start with topno=0
connect by prior no=topnoSQL> with tb as
2 (select 1 no,1 leve,0 topno from dual union all
3 select 2,1 ,0 from dual union all
4 select 3 ,2, 1 from dual union all
5 select 4 ,2, 2 from dual union all
6 select 5 ,2, 1 from dual union all
7 select 6 ,2, 2 from dual )
8 select no,leve,topno from tb
9 start with topno=0
10 connect by prior no=topno
11 /
NO LEVE TOPNO
---------- ---------- ----------
1 1 0
3 2 1
5 2 1
2 1 0
4 2 2
6 2 2
6 rows selected
--结果
1 1 1 0
2 3 2 1
3 5 2 1
4 2 1 0
5 4 2 2
6 6 2 2
2 select 1 no,1 wlevel,0 topno from dual union all
3 select 4, 2, 2 from dual union all
4 select 5, 2, 1 from dual union all
5 select 3, 2, 1 from dual union all
6 select 2, 1, 0 from dual union all
7 select 6, 2, 2 from dual)
8 select * from tb
9 connect by nocycle topno=prior no
10 start with topno=0
11 ;
NO WLEVEL TOPNO
---------- ---------- ----------
1 1 0
5 2 1
3 2 1
2 1 0
4 2 2
6 2 2
6 rows selected
SQL>
不加order by是不成的.不加的话按你的自然排序去排列的