这是从下往上搜索的 select level,sys_connect_by_path(col1,'/') path from table where col1 = 输入你要的父节点col1比如:'1683' start with **输入你要求的叶节点条件随便输** 比如 col1='1706' 或者 col2 ='1705' 或者 col3 = '5' connect by col1 = prior col2从上往下搜索的 差别不大 自己写吧
所有叶子: select * from ( SELECT nodeid,level FROM test_table CONNECT BY PRIOR nodeid = DECODE(nodeid, parentnodeid, NULL, parentnodeid) START WITH nodeid = 3 ORDER BY level, nodeid; ) a where not exists (select 1 from test_table b where b.parentnodeid = a.nodeid)
要达到这种要求,和SQL关系不大,改一下数据表设计就可以了
拿你提供的数据做的范例: CREATE TABLE TBL_C(ID NUMBER, PARENTID NUMBER, LEV INTEGER);INSERT INTO TBL_C SELECT 1683,1,1 FROM DUAL UNION ALL SELECT 1703,1683,2 FROM DUAL UNION ALL SELECT 1707,1683,2 FROM DUAL UNION ALL SELECT 1708,1683,2 FROM DUAL UNION ALL SELECT 5759,1683,2 FROM DUAL UNION ALL SELECT 7729,1683,2 FROM DUAL UNION ALL SELECT 7733,1683,2 FROM DUAL UNION ALL SELECT 7752,1683,2 FROM DUAL UNION ALL SELECT 1704,1703,3 FROM DUAL UNION ALL SELECT 1709,1708,3 FROM DUAL UNION ALL SELECT 7753,7729,3 FROM DUAL UNION ALL SELECT 7754,7752,3 FROM DUAL UNION ALL SELECT 1705,1704,4 FROM DUAL UNION ALL SELECT 5179,1709,4 FROM DUAL UNION ALL SELECT 5229,1709,4 FROM DUAL UNION ALL SELECT 1706,1705,5 FROM DUAL; SELECT * FROM TBL_C START WITH LEV = 1 CONNECT BY PARENTID = PRIOR ID ORDER BY LEV
oracle10g提供解决方案: --创建测试表,增加测试数据create table test(superid varchar2(20),id varchar2(20));insert into test values('0','1'); insert into test values('0','2');insert into test values('1','11'); insert into test values('1','12');insert into test values('2','21'); insert into test values('2','22');insert into test values('11','111'); insert into test values('11','112');insert into test values('12','121'); insert into test values('12','122');insert into test values('21','211'); insert into test values('21','212');insert into test values('22','221'); insert into test values('22','222');commit; select level||'层' ,lpad(' ',level*5)||id id ,CONNECT_BY_ISLEAF from test where CONNECT_BY_ISLEAF = 1 start with superid = '0' connect by prior id=superid;自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。 那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”, 如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。
2 CONNECT BY PRIOR nodeid =
3 DECODE(nodeid, parentnodeid, NULL, parentnodeid)
4 START WITH nodeid = 4
5 ORDER BY level, nodeid;
NODEID LEVEL
--------- ---------
4 2
1 3
2 3
6 4
SQL> SELECT nodeid,level FROM test_table
2 CONNECT BY PRIOR nodeid =
3 DECODE(nodeid, parentnodeid, NULL, parentnodeid)
4 START WITH nodeid = 3
5 ORDER BY level, nodeid;
NODEID LEVEL
--------- ---------
3 1
4 2
8 2
1 3
2 3
7 3
9 3
6 4
10 4
9 rows selected.
START WITH nodeid = 3
这行不就是指定了一个节点么
只要叶子的话限制level的值就好了
1683|1|1
1703|1683|2
1707|1683|2
1708|1683|2
5759|1683|2
7729|1683|2
7733|1683|2
7752|1683|2
1704|1703|3
1709|1708|3
7753|7729|3
7754|7752|3
1705|1704|4
5179|1709|4
5229|1709|4
1706|1705|5
select level,sys_connect_by_path(col1,'/') path
from table
where col1 = 输入你要的父节点col1比如:'1683'
start with **输入你要求的叶节点条件随便输**
比如 col1='1706' 或者 col2 ='1705' 或者 col3 = '5'
connect by col1 = prior col2从上往下搜索的
差别不大 自己写吧
select *
from
(
SELECT nodeid,level FROM test_table
CONNECT BY PRIOR nodeid =
DECODE(nodeid, parentnodeid, NULL, parentnodeid)
START WITH nodeid = 3
ORDER BY level, nodeid;
) a
where not exists (select 1 from test_table b where b.parentnodeid = a.nodeid)
CREATE TABLE TBL_C(ID NUMBER, PARENTID NUMBER, LEV INTEGER);INSERT INTO TBL_C
SELECT 1683,1,1 FROM DUAL UNION ALL
SELECT 1703,1683,2 FROM DUAL UNION ALL
SELECT 1707,1683,2 FROM DUAL UNION ALL
SELECT 1708,1683,2 FROM DUAL UNION ALL
SELECT 5759,1683,2 FROM DUAL UNION ALL
SELECT 7729,1683,2 FROM DUAL UNION ALL
SELECT 7733,1683,2 FROM DUAL UNION ALL
SELECT 7752,1683,2 FROM DUAL UNION ALL
SELECT 1704,1703,3 FROM DUAL UNION ALL
SELECT 1709,1708,3 FROM DUAL UNION ALL
SELECT 7753,7729,3 FROM DUAL UNION ALL
SELECT 7754,7752,3 FROM DUAL UNION ALL
SELECT 1705,1704,4 FROM DUAL UNION ALL
SELECT 5179,1709,4 FROM DUAL UNION ALL
SELECT 5229,1709,4 FROM DUAL UNION ALL
SELECT 1706,1705,5 FROM DUAL;
SELECT *
FROM TBL_C
START WITH LEV = 1
CONNECT BY PARENTID = PRIOR ID
ORDER BY LEV
--创建测试表,增加测试数据create table test(superid varchar2(20),id varchar2(20));insert into test values('0','1');
insert into test values('0','2');insert into test values('1','11');
insert into test values('1','12');insert into test values('2','21');
insert into test values('2','22');insert into test values('11','111');
insert into test values('11','112');insert into test values('12','121');
insert into test values('12','122');insert into test values('21','211');
insert into test values('21','212');insert into test values('22','221');
insert into test values('22','222');commit;
select level||'层'
,lpad(' ',level*5)||id id
,CONNECT_BY_ISLEAF
from test
where CONNECT_BY_ISLEAF = 1
start with superid = '0'
connect by prior id=superid;自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”,
如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。