还是用原来的例子:
1、假定子树的父结点的id号为1:
SQL> select level,parent_id,id, name
2 from sample x
3 connect by prior id = parent_id
4 start with parent_id = 1; --此处定义起点的PARENT_ID为1 LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
1 1 2 name2
2 2 3 name3
2 2 4 name4
1 1 5 name5
2 5 6 name6
2 5 7 name72、下面的语法能成功,但不知道是不是最好的:
SQL> select level,parent_id,id, name
2 from sample x
3 where level in (select max(level) from sample
4 connect by prior id = parent_id
5 start with parent_id = 1)
6 connect by prior id = parent_id
7 start with parent_id = 1; LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
2 2 3 name3
2 2 4 name4
2 5 6 name6
2 5 7 name73、把where语句放在connect by之前
SQL> select level,parent_id,id, name
2 from sample x
3 where name in ('name2','name6')
4 connect by prior id = parent_id
5 start with parent_id = 1
6 ; LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
1 1 2 name2
2 5 6 name6
1、假定子树的父结点的id号为1:
SQL> select level,parent_id,id, name
2 from sample x
3 connect by prior id = parent_id
4 start with parent_id = 1; --此处定义起点的PARENT_ID为1 LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
1 1 2 name2
2 2 3 name3
2 2 4 name4
1 1 5 name5
2 5 6 name6
2 5 7 name72、下面的语法能成功,但不知道是不是最好的:
SQL> select level,parent_id,id, name
2 from sample x
3 where level in (select max(level) from sample
4 connect by prior id = parent_id
5 start with parent_id = 1)
6 connect by prior id = parent_id
7 start with parent_id = 1; LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
2 2 3 name3
2 2 4 name4
2 5 6 name6
2 5 7 name73、把where语句放在connect by之前
SQL> select level,parent_id,id, name
2 from sample x
3 where name in ('name2','name6')
4 connect by prior id = parent_id
5 start with parent_id = 1
6 ; LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
1 1 2 name2
2 5 6 name6
LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
1 1 2 name2
2 2 3 name3
2 2 4 name4
现在我知道了一个结点如下:
PARENT_ID ID NAME
---------- ---------- --------------------------
1 2 name2通过这个我想要得到如下 LEVEL PARENT_ID ID NAME
---------- ---------- ---------- --------------------------
1 1 2 name2
2 2 3 name3
2 2 4 name4
而整个树形结构是:
ID NAME PARENT_ID
---------- ---------- ---------- --------------------------
1 name1 0
2 name2 1
3 name3 2
4 name4 2
5 name5 1
6 name6 5
7 name7 5
SQL> select level,parent_id,id, name
2 from sample x
3 connect by prior id = parent_id
4 start with id = 2; LEVEL PARENT_ID ID NAME
---------- ---------- ---------- -----------------------------
1 1 2 name2
2 2 3 name3
2 2 4 name4
对不起,写错了,请大哥不要见笑
已经说的很清楚了,
关于排序,你试一下不就知道了,
我这里也没有这样的环境,没有试过能否排序。