Start with connect by prior connect_by_isleaf

In 10 Gb, Oracle provides a new pseudo column: connect_by_isleaf. This pseudo column can be used to determine whether the current record is a leaf node of the tree.

This section describes how to implement the corresponding functions in 9i.

First, construct an example:

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
2 2 1 BC
3 3 1 de
4 4 1 FG
5 5 2 hij
6 6 4 KLM
7 7 6 nopq

 

 

Let's take a look at the functions of 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 can determine whether the current record is a leaf node of the tree. This function is not implemented in 9i and can only be determined by analyzing functions:

 

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

The analytic function can be used to implement the connect_by_isleaf pseudo column function in 9i.

 

Or you can use the select ID,
Templatename,
Templateshowname,
Templatetype,
Templatepath,
Supportbox,
Isdefault,
Pagesize,
Pagestringlength,
Templateshowimagename,
Templatelevel,
Leveldefault,
Parentid,
(Select count (*) from t_template A where. parentid = B. ID) leaf ************************* note the following:
From t_template B
Start with ID = 0
Connect by prior id = parentid

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/red_hot/archive/2009/12/25/5074262.aspx