oracle 9i判斷是葉子或根節(jié)點(diǎn)，是比較麻煩的一件事情，SQL演示腳本如下:
view plaincopy to clipboardprint?
DROP ?TABLE ?idb_hierarchical;??create ?TABLE ?idb_hierarchical??(?? id?number,?? parent_id?number,?? str?varchar2(10)?? );?? ?? insert ?into ?idb_hierarchical?values (1,null ,'A' );??insert ?into ?idb_hierarchical?values (2,1,'B' );??insert ?into ?idb_hierarchical?values (3,2,'C' );??insert ?into ?idb_hierarchical?values (4,3,'D' );??insert ?into ?idb_hierarchical?values (5,2,'E' );??insert ?into ?idb_hierarchical?values (6,2,'F' );??insert ?into ?idb_hierarchical?values (7,3,'G' );??insert ?into ?idb_hierarchical?values (8,4,'H' );??insert ?into ?idb_hierarchical?values (9,4,'I' );??insert ?into ?idb_hierarchical?values (10,null ,'J' );??insert ?into ?idb_hierarchical?values (11,10,'K' );??insert ?into ?idb_hierarchical?values (12,11,'L' );??insert ?into ?idb_hierarchical?values (13,10,'M' );??
DROP TABLE idb_hierarchical;
create TABLE idb_hierarchical
(
id number,
parent_id number,
str varchar2(10)
);insert into idb_hierarchical values(1,null,'A');
insert into idb_hierarchical values(2,1,'B');
insert into idb_hierarchical values(3,2,'C');
insert into idb_hierarchical values(4,3,'D');
insert into idb_hierarchical values(5,2,'E');
insert into idb_hierarchical values(6,2,'F');
insert into idb_hierarchical values(7,3,'G');
insert into idb_hierarchical values(8,4,'H');
insert into idb_hierarchical values(9,4,'I');
insert into idb_hierarchical values(10,null,'J');
insert into idb_hierarchical values(11,10,'K');
insert into idb_hierarchical values(12,11,'L');
insert into idb_hierarchical values(13,10,'M');
示例數(shù)據(jù)清單如下:
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,LEVEL *2+1,'.' )||STR?STR_LEVEL,ID,PARENT_ID,LEVEL ?LVL????FROM ?idb_hierarchical?? ?START?WITH ?PARENT_ID?IS ?NULL ?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVLFROM idb_hierarchicalSTART WITH PARENT_ID IS NULL
CONNECT BY PARENT_ID = PRIOR ID;
表1:數(shù)據(jù)清單STR_LEVELIDPARENT_IDLVL +..A 1 ? 1 +….B 2 1 2 +……C 3 2 3 +……..D 4 3 4 +……….H 8 4 5 +……….I 9 4 5 +……..G 7 3 4 +……E 5 2 3 +……F 6 2 3 +..J 10 ? 1 +….K 11 10 2 +……L 12 11 3 +….M 13 10 2
在表1中，ID為8、9、 7、5、6、12、13都沒(méi)有子節(jié)點(diǎn)，因此稱(chēng)為葉節(jié)點(diǎn)。
1.oracle9i 查詢(xún)?nèi)~節(jié)點(diǎn)
只顯示葉子節(jié)點(diǎn)SQL
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,LEVEL *2+1,'.' )||STR?STR_LEVEL,ID,PARENT_ID,LEVEL ?LVL????FROM ?idb_hierarchical?I?? ???? ??WHERE ?NOT ?EXISTS(SELECT ?1?? ??FROM ?idb_hierarchical?B?? ??WHERE ?I.ID=B.PARENT_ID)?? ?START?WITH ?PARENT_ID?IS ?NULL ?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVLFROM idb_hierarchical I--在oracle 9i中顯示葉節(jié)點(diǎn)，需要判斷是否有子節(jié)點(diǎn)即可WHERE NOT EXISTS(SELECT 1FROM idb_hierarchical BWHERE I.ID=B.PARENT_ID)START WITH PARENT_ID IS NULL
CONNECT BY PARENT_ID = PRIOR ID;
表2STR_LEVEL ID PARENT_ID LVL +……….H 8 4 5 +……….I 9 4 5 +……..G 7 3 4 +……E 5 2 3 +……F 6 2 3 +……L 12 11 3 +….M 13 10 2
顯示所有節(jié)點(diǎn)，標(biāo)明該行是否為葉節(jié)點(diǎn)SQL
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,LEVEL *2+1,'.' )||STR?STR_LEVEL,ID,PARENT_ID,LEVEL ?LVL,??NVL((SELECT ?'N' ?? ??FROM ?idb_hierarchical?B?? ??WHERE ?I.ID=B.PARENT_ID?? ??AND ?ROWNUM??<?2),'Y' )?IS_LEAF?? ??FROM ?idb_hierarchical?I?? ?START?WITH ?PARENT_ID?IS ?NULL ?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,
NVL((SELECT 'N'FROM idb_hierarchical BWHERE I.ID=B.PARENT_IDAND ROWNUM < 2),'Y') IS_LEAFFROM idb_hierarchical ISTART WITH PARENT_ID IS NULL
CONNECT BY PARENT_ID = PRIOR ID;
表3STR_LEVEL ID PARENT_ID LVL IS_LEAF +..A 1 ? 1 N +....B 2 1 2 N +......C 3 2 3 N +........D 4 3 4 N +..........H 8 4 5 Y +..........I 9 4 5 Y +........G 7 3 4 Y +......E 5 2 3 Y +......F 6 2 3 Y +..J 10 ? 1 N +....K 11 10 2 N +......L 12 11 3 Y +....M 13 10 2 Y
oracle 9i 查詢(xún)根節(jié)點(diǎn)
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,LEVEL *2+1,'.' )||STR?STR_LEVEL,ID,PARENT_ID,LEVEL ?LVL????FROM ?idb_hierarchical?I?? ?START?WITH ?id?=2?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVLFROM idb_hierarchical ISTART WITH id =2
CONNECT BY PARENT_ID = PRIOR ID;
表4STR_LEVEL ID PARENT_ID LVL +..B 2 1 1 +....C 3 2 2 +......D 4 3 3 +........H 8 4 4 +........I 9 4 4 +......G 7 3 3 +....E 5 2 2 +....F 6 2 2
根節(jié)點(diǎn)ID應(yīng)該為3、5、6，即lvl為1即可
查詢(xún)根節(jié)點(diǎn)，只顯示根節(jié)點(diǎn)SQL
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,?LEVEL ?*?2?+?1,?'.' )?||?STR?STR_LEVEL,?????????ID,?? ???????PARENT_ID,?? ???????LEVEL ?LVL,?? ???????(select ?b.str?? ??????????from ?idb_hierarchical?b?? ?????????where ?level ?=?1?? ?????????start?with ?b.id?=?2?? ????????connect ?by ?prior ?b.id?=??b.parent_id?? ????????)?root_str?? ??FROM ?idb_hierarchical?I?? ?where ?level ?=?1?? ?START?WITH ?id?=?2?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,(select b.strfrom idb_hierarchical bwhere level = 1start with b.id = 2connect by prior b.id = b.parent_id) root_strFROM idb_hierarchical Iwhere level = 1START WITH id = 2
CONNECT BY PARENT_ID = PRIOR ID;
表5STR_LEVEL ID PARENT_ID LVL ROOT_STR +..B 2 1 1 B
標(biāo)明根節(jié)點(diǎn)SQL
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,?LEVEL ?*?2?+?1,?'.' )?||?STR?STR_LEVEL,?????????ID,?? ???????PARENT_ID,?? ???????DECODE(LEVEL ,?1,?'Y' ,?'N' )?is_root,?? ???????LEVEL ?LVL,?? ???????(select ?b.str?? ??????????from ?idb_hierarchical?b?? ?????????where ?level ?=?1?? ?????????start?with ?b.id?=?2?? ????????connect ?by ?prior ?b.id?=?b.parent_id)?root_str?? ??FROM ?idb_hierarchical?I?? ?START?WITH ?id?=?2?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,ID,PARENT_ID,DECODE(LEVEL, 1, 'Y', 'N') is_root,LEVEL LVL,(select b.strfrom idb_hierarchical bwhere level = 1start with b.id = 2connect by prior b.id = b.parent_id) root_strFROM idb_hierarchical ISTART WITH id = 2
CONNECT BY PARENT_ID = PRIOR ID;
表6STR_LEVEL ID PARENT_ID IS_ROOT LVL ROOT_STR +..B 2 1 Y 1 B +....C 3 2 N 2 B +......D 4 3 N 3 B +........H 8 4 N 4 B +........I 9 4 N 4 B +......G 7 3 N 3 B +....E 5 2 N 2 B +....F 6 2 N 2 B
在oracle 10g提供了connect_by_isleaf和connect_by_root
oracle 10g用connect_by_isleaf判斷葉節(jié)點(diǎn)
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,LEVEL *2+1,'.' )||STR?STR_LEVEL,ID,PARENT_ID,LEVEL ?LVL????FROM ?idb_hierarchical?I?? where ?connect_by_isleaf=1???START?WITH ?PARENT_ID?IS ?NULL ?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVLFROM idb_hierarchical I
where connect_by_isleaf=1START WITH PARENT_ID IS NULL
CONNECT BY PARENT_ID = PRIOR ID;
表7STR_LEVEL ID PARENT_ID LVL +..........H 8 4 5 +..........I 9 4 5 +........G 7 3 4 +......E 5 2 3 +......F 6 2 3 +......L 12 11 3 +....M 13 10 2
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,LEVEL *2+1,'.' )||STR?STR_LEVEL,ID,PARENT_ID,LEVEL ?LVL,??decode(connect_by_isleaf,1,'Y' ,'N' )?IS_LEAF?? ??FROM ?idb_hierarchical?I?? ?START?WITH ?PARENT_ID?IS ?NULL ?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,
decode(connect_by_isleaf,1,'Y','N') IS_LEAFFROM idb_hierarchical ISTART WITH PARENT_ID IS NULL
CONNECT BY PARENT_ID = PRIOR ID;
表8STR_LEVEL ID PARENT_ID LVL IS_LEAF +..A 1 ? 1 N +....B 2 1 2 N +......C 3 2 3 N +........D 4 3 4 N +..........H 8 4 5 Y +..........I 9 4 5 Y +........G 7 3 4 Y +......E 5 2 3 Y +......F 6 2 3 Y +..J 10 ? 1 N +....K 11 10 2 N +......L 12 11 3 Y +....M 13 10 2 Y
oracle 10g用connect_by_root判斷根節(jié)點(diǎn)
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,?LEVEL ?*?2?+?1,?'.' )?||?STR?STR_LEVEL,?????????ID,?? ???????PARENT_ID,?? ???????LEVEL ?LVL,?? ???????connect_by_root?STR?ROOT_STR?? ??FROM ?idb_hierarchical?I?? ?START?WITH ?id?=?2?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,connect_by_root STR ROOT_STRFROM idb_hierarchical ISTART WITH id = 2
CONNECT BY PARENT_ID = PRIOR ID;
表9STR_LEVELIDPARENT_IDLVLROOT_STR +..B 2 1 1 B +....C 3 2 2 B +......D 4 3 3 B +........H 8 4 4 B +........I 9 4 4 B +......G 7 3 3 B +....E 5 2 2 B +....F 6 2 2 B
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,?LEVEL ?*?2?+?1,?'.' )?||?STR?STR_LEVEL,?????????ID,?? ???????PARENT_ID,?? ???????DECODE(LEVEL ,?1,?'Y' ,?'N' )?is_root,?? ???????LEVEL ?LVL,?? ???????connect_by_root?STR?ROOT_STR?? ??FROM ?idb_hierarchical?I?? ?START?WITH ?id?=?3?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,ID,PARENT_ID,DECODE(LEVEL, 1, 'Y', 'N') is_root,LEVEL LVL,connect_by_root STR ROOT_STRFROM idb_hierarchical ISTART WITH id = 3
CONNECT BY PARENT_ID = PRIOR ID;
表10STR_LEVELIDPARENT_IDIS_ROOTLVLROOT_STR +..C 3 2 Y 1 C +....D 4 3 N 2 C +......H 8 4 N 3 C +......I 9 4 N 3 C +....G 7 3 N 2 C
view plaincopy to clipboardprint?
SELECT ?RPAD('+' ,?LEVEL ?*?2?+?1,?'.' )?||?STR?STR_LEVEL,?????????ID,?? ???????PARENT_ID,?? ???????DECODE(LEVEL ,?1,?'Y' ,?'N' )?is_root,?? ???????LEVEL ?LVL,?? ???????connect_by_root?STR?ROOT_STR?? ??FROM ?idb_hierarchical?I?? ?START?WITH ?PARENT_ID?=?2?? CONNECT ?BY ?PARENT_ID?=?PRIOR ?ID;??
SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,ID,PARENT_ID,DECODE(LEVEL, 1, 'Y', 'N') is_root,LEVEL LVL,connect_by_root STR ROOT_STRFROM idb_hierarchical ISTART WITH PARENT_ID = 2
CONNECT BY PARENT_ID = PRIOR ID;
表11STR_LEVELIDPARENT_IDIS_ROOTLVLROOT_STR +..C 3 2 Y 1 C +....D 4 3 N 2 C +......H 8 4 N 3 C +......I 9 4 N 3 C +....G 7 3 N 2 C +..E 5 2 Y 1 E +..F 6 2 Y 1 F
總結(jié)
以上是生活随笔 為你收集整理的oracle 层次查询判断叶子和根节点 的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
如果覺(jué)得生活随笔 網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔 推薦給好友。
