咔咔,怎么100分的问题都没人答啊??16:50:07 SQL> desc t; 名称 空? 类型 ----------------------------------------- -------- --------------------------- ID NUMBER(38) PID NUMBER(38)16:50:12 SQL> select * from t; ID PID ---------- ---------- 1 2 1 3 1 4 2 5 3已用时间: 00: 00: 00.10 16:50:14 SQL> select id 16:50:30 2 from t 16:50:30 3 start with id = 1 16:50:30 4 connect by prior id = pid 16:50:30 5 intersect 16:50:30 6 select id 16:50:30 7 from t 16:50:30 8 where id not in (select pid id from t where pid is not null); ID ---------- 4 5已用时间: 00: 00: 00.00
测试: SQL> select * from t_tree; ID PID ---------- ---------- 1 0 11 1 12 1 111 11 112 11 1121 112已选择6行。已用时间: 00: 00: 00.70 //选择11下面所有的字节点 从结果可以看出 111,1121是最终叶子节点 SQL> select * from t_tree start with id=11 connect by prior id=pid; ID PID ---------- ---------- 11 1 111 11 112 11 1121 112已用时间: 00: 00: 00.20SQL> select id from ( 2 select * from t_tree start with id=11 connect by prior id=pid) 3 minus 4 select pid from ( 5 select * from t_tree start with id=11 connect by prior id=pid); ID ---------- 111 1121已用时间: 00: 00: 00.40 SQL> OK!
所谓最底层的节点是否就是叶子节点?如果是,9i下可以这样做 SQL> select * from t_test; ID PID ---------- ---------- 1 2 1 3 1 4 2 5 3 10 11 10 12 10 13 11 14 11已选择10行。SQL> select * 2 from (select * from t_test 3 connect by prior id=pid 4 start with id=1) a 5 where not exists(select 1 from t_test b where b.pid=a.id); ID PID ---------- ---------- 4 2 5 3SQL> select * 2 from (select * from t_test 3 connect by prior id=pid 4 start with id=10) a 5 where not exists(select 1 from t_test b where b.pid=a.id); ID PID ---------- ---------- 13 11 14 11 12 10
SQL> select * from t;BILL_ITEM_ID BILL_PARENT_ID ------------ -------------- 1 2 1 3 1 4 2 5 3 6 5 7 8 7 9 8 10 910 rows selectedSQL> SELECT DISTINCT first_value(bill_item_id) over(PARTITION BY ROWNUM - LEVEL ORDER BY LEVEL DESC) 2 FROM t 3 START WITH bill_item_id = 1 4 CONNECT BY PRIOR bill_item_id = bill_parent_id 5 /FIRST_VALUE(BILL_ITEM_ID)OVER( ------------------------------ 4 6SQL> SELECT DISTINCT first_value(bill_item_id) over(PARTITION BY ROWNUM - LEVEL ORDER BY LEVEL DESC) 2 FROM t 3 START WITH bill_item_id = 7 4 CONNECT BY PRIOR bill_item_id = bill_parent_id 5 /FIRST_VALUE(BILL_ITEM_ID)OVER( ------------------------------ 10
名称 空? 类型
----------------------------------------- -------- --------------------------- ID NUMBER(38)
PID NUMBER(38)16:50:12 SQL> select * from t; ID PID
---------- ----------
1
2 1
3 1
4 2
5 3已用时间: 00: 00: 00.10
16:50:14 SQL> select id
16:50:30 2 from t
16:50:30 3 start with id = 1
16:50:30 4 connect by prior id = pid
16:50:30 5 intersect
16:50:30 6 select id
16:50:30 7 from t
16:50:30 8 where id not in (select pid id from t where pid is not null); ID
----------
4
5已用时间: 00: 00: 00.00
SQL> select * from t_tree; ID PID
---------- ----------
1 0
11 1
12 1
111 11
112 11
1121 112已选择6行。已用时间: 00: 00: 00.70
//选择11下面所有的字节点 从结果可以看出 111,1121是最终叶子节点
SQL> select * from t_tree start with id=11 connect by prior id=pid; ID PID
---------- ----------
11 1
111 11
112 11
1121 112已用时间: 00: 00: 00.20SQL> select id from (
2 select * from t_tree start with id=11 connect by prior id=pid)
3 minus
4 select pid from (
5 select * from t_tree start with id=11 connect by prior id=pid); ID
----------
111
1121已用时间: 00: 00: 00.40
SQL> OK!
SQL> select * from t_test; ID PID
---------- ----------
1
2 1
3 1
4 2
5 3
10
11 10
12 10
13 11
14 11已选择10行。SQL> select *
2 from (select * from t_test
3 connect by prior id=pid
4 start with id=1) a
5 where not exists(select 1 from t_test b where b.pid=a.id); ID PID
---------- ----------
4 2
5 3SQL> select *
2 from (select * from t_test
3 connect by prior id=pid
4 start with id=10) a
5 where not exists(select 1 from t_test b where b.pid=a.id); ID PID
---------- ----------
13 11
14 11
12 10
------------ --------------
1
2 1
3 1
4 2
5 3
6 5
7
8 7
9 8
10 910 rows selectedSQL> SELECT DISTINCT first_value(bill_item_id) over(PARTITION BY ROWNUM - LEVEL ORDER BY LEVEL DESC)
2 FROM t
3 START WITH bill_item_id = 1
4 CONNECT BY PRIOR bill_item_id = bill_parent_id
5 /FIRST_VALUE(BILL_ITEM_ID)OVER(
------------------------------
4
6SQL> SELECT DISTINCT first_value(bill_item_id) over(PARTITION BY ROWNUM - LEVEL ORDER BY LEVEL DESC)
2 FROM t
3 START WITH bill_item_id = 7
4 CONNECT BY PRIOR bill_item_id = bill_parent_id
5 /FIRST_VALUE(BILL_ITEM_ID)OVER(
------------------------------
10