Article From:https://www.cnblogs.com/-beauTiFul/p/9121553.html

 

Base table creation:

with temp as (
select 'company' name, '1' id, '' pid from dual union all
select 'Department 1'  name , '11' id, '1' pid from dual union all
select 'Department 2'  name , '12' id, '1' pid from dual union all
select 'Employee 11' name , '111' id , '11' pid from dual union all
select 'Employee 12' name , '112' id , '11' pid from dual union all
select 'Employee 21' name , '121' id , '12' pid from dual 
)

The field on the left side of the sign is based on the query to the right field = the left field.

levelCan see the distance from the parent node

Look up from the top

The root node can get all the child nodes including the root node and below.
select temp.*,level from temp
connect by prior id= pid
start with id='1'

 

Multiple selection of root nodes
select * from temp
connect by prior  id= pid
start with id in ('11' ,'12')

Add the where statement

select * from temp
where id = '111'
connect by prior  id= pid
start with id in ('11' ,'12')

 From the bottom up

select * from temp
connect by prior pid= id 
start with id = '121'

 

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *