求在ORACLE9i下查询树的叶子结点的SQL语句.在线等(......................................)

[riso8371]

如题:
求在ORACLE9i下查询树的叶子结点的SQL语句

请不要将10G特性中的东东来解答.因为我用的数据库必需是9i

[092066]

如果你需要的是oracle9i的语法树,请联系[e-mail]

[蒋晟]

SQL> SELECT nodeid,level FROM test_table
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.

[riso8371]

怎么限制,有些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

[riso8371]

to jiangsheng(蒋晟.Net[MVP]) ( )

你这个查询只是按树的结点层次显示下来,这样的话,我还是要在程序中处理,能不能一次性得到所规定结点下的所有叶子结点

[蒋晟]

什么意思
START WITH nodeid = 3
这行不就是指定了一个节点么
只要叶子的话限制level的值就好了

[mesmes]

这是从下往上搜索的
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

从上往下搜索的
差别不大 自己写吧

[DragonBill]

要达到这种要求,和SQL关系不大,改一下数据表设计就可以了

[liuyxit]

所有叶子：
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)

[zqz117]

关注一下

[DragonBill]

拿你提供的数据做的范例:
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

[beckhambobo]

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”。

[beckhambobo]

sorry,看错

[baojianjun]

哈哈