start with connect by prior 中新加的 CONNECT_BY_ISLEAF

 在10g中Oracle提供了新的伪列：CONNECT_BY_ISLEAF，通过这个伪列，可以判断当前的记录是否是树的叶节点。

这里描述一下在9i中如何实现相应的功能.

首先构造一个例子：

 view plaincopy to clipboardprint?
CREATE TABLE T_TREE (ID NUMBER PRIMARY KEY, FATHER_ID NUMBER, NAME VARCHAR2(30));  
INSERT INTO T_TREE VALUES (1, 0, 'A');  
INSERT INTO T_TREE VALUES (2, 1, 'BC');  
INSERT INTO T_TREE VALUES (3, 1, 'DE');  
INSERT INTO T_TREE VALUES (4, 1, 'FG');  
INSERT INTO T_TREE VALUES (5, 2, 'HIJ');  
INSERT INTO T_TREE VALUES (6, 4, 'KLM');  
INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');  
COMMIT; 
CREATE TABLE T_TREE (ID NUMBER PRIMARY KEY, FATHER_ID NUMBER, NAME VARCHAR2(30));
INSERT INTO T_TREE VALUES (1, 0, 'A');
INSERT INTO T_TREE VALUES (2, 1, 'BC');
INSERT INTO T_TREE VALUES (3, 1, 'DE');
INSERT INTO T_TREE VALUES (4, 1, 'FG');
INSERT INTO T_TREE VALUES (5, 2, 'HIJ');
INSERT INTO T_TREE VALUES (6, 4, 'KLM');
INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');
COMMIT;

 

select * from t_tree;

 

    ID FATHER_ID NAME
1 1 0 A
2 2 1 BC
3 3 1 DE
4 4 1 FG
5 5 2 HIJ
6 6 4 KLM
7 7 6 NOPQ

 

 

看看CONNECT_BY_ISLEAF的功能：

 

view plaincopy to clipboardprint?
SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
FROM T_TREE   
START WITH FATHER_ID = 0   
CONNECT BY PRIOR ID = FATHER_ID; 
SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF
FROM T_TREE
START WITH FATHER_ID = 0
CONNECT BY PRIOR ID = FATHER_ID;
 

 

    ID FATHER_ID NAME LEAF
1 1 0 A 0
2 2 1 BC 0
3 5 2 HIJ 1
4 3 1 DE 1
5 4 1 FG 0
6 6 4 KLM 0
7 7 6 NOPQ 1

 

view plaincopy to clipboardprint?
SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
FROM T_TREE  
START WITH ID = 7  
ONNECT BY PRIOR FATHER_ID = ID; 
 SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF
 FROM T_TREE
 START WITH ID = 7
 ONNECT BY PRIOR FATHER_ID = ID;

    ID FATHER_ID NAME LEAF
1 7 6 NOPQ 0
2 6 4 KLM 0
3 4 1 FG 0
4 1 0 A 1

 

CONNECT_BY_ISLEAF可以判断当前记录是否是树的叶节点。而这个功能在9i中没有简单的方法来实现，只能通过分析函数来进行判断：

 

view plaincopy to clipboardprint?
SELECT   
 ID,  
 FATHER_ID,  
 NAME,  
 CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF  
 FROM  
 (  
 SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS  
 FROM T_TREE  
 START WITH FATHER_ID = 0  
 CONNECT BY PRIOR ID = FATHER_ID  
 ); 
SELECT
 ID,
 FATHER_ID,
 NAME,
 CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
 FROM
 (
 SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
 FROM T_TREE
 START WITH FATHER_ID = 0
 CONNECT BY PRIOR ID = FATHER_ID
 );

      ID FATHER_ID NAME LEAF
1 1 0 A 0
2 2 1 BC 0
3 5 2 HIJ 1
4 3 1 DE 1
5 4 1 FG 0
6 6 4 KLM 0
7 7 6 NOPQ 1

 

view plaincopy to clipboardprint?
SELECT   
 ID,  
 FATHER_ID,  
 NAME,   
 CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF  
 FROM  
 (   
 SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS  
 FROM T_TREE  
 START WITH ID = 7   
 CONNECT BY PRIOR FATHER_ID = ID  
 ); 
SELECT
 ID,
 FATHER_ID,
 NAME,
 CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
 FROM
 (
 SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
 FROM T_TREE
 START WITH ID = 7
 CONNECT BY PRIOR FATHER_ID = ID
 );

      ID FATHER_ID NAME LEAF
1 7 6 NOPQ 0
2 6 4 KLM 0
3 4 1 FG 0
4 1 0 A 1

 利用分析函数可以相对简单的在9i实现CONNECT_BY_ISLEAF伪列的功能。

 

或者可以使用select id,
       templatename,
       templateshowname,
       templatetype,
       templatepath,
       supportbox,
       isdefault,
       pagesize,
       pagestringlength,
       templateshowimagename,
       templatelevel,
       leveldefault,
       parentid ,
       (select count(*) from t_template a where a.parentid=b.id) leaf  ***********************注意这里
  from t_template b
  start with id = 0
  connect by prior id = parentid

 

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/red_hot/archive/2009/12/25/5074262.aspx