Oracle connect by parent-child line, position level

connect by is used for hierarchical query of data tables with hierarchical relationships such as parent-child, grandson, parent-child, etc.

Syntax format:
{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ] | START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...}

Explanation of special words:

start with: Specifies the conditions for the start node

connect by: Specifies the conditional relationship of parent-child rows

Prior: qualifier of query parent row, format: prior column1 = column2 or column1 = prior column2 and,

nocycle: if there is a circular row in the data table, an error will be reported if this keyword is not added. After adding a keyword, no error will be reported, but only the first one of the two rows of the circular will be displayed

Circular row: this row has only one child row, and the child row is the parent row of this row

Connect by iscycle: precondition: this keyword can only be used after nocycle is used. It is used to indicate whether it is a circular line, 0 indicates no, and 1 indicates yes

Connect by IsLeaf: whether it is a leaf node, 0 for no, 1 for yes

level: level pseudo column, indicating the level. The lower the value, the higher the level. level=1 is the highest level node

Custom data:

-- Create table
create table TEST_EMPLOYEE(
	EMP_ID number,--Staff coding
	LEAD_ID number,--Higher level coding
	EMP_NAME varchar2(50),--Employee name
	SALARY number,--Employee salaries
	DEPT_NO varchar2(8)--Department number
	);

-- Add data
insert into TEST_EMPLOYEE values('1',0,'king','1000000.00','001');
insert into TEST_EMPLOYEE values('2',1,'jack','50500.00','002');
insert into TEST_EMPLOYEE values('3',1,'arise','60000.00','003');
insert into TEST_EMPLOYEE values('4',2,'scott','30000.00','002');
insert into TEST_EMPLOYEE values('5',2,'tiger','25000.00','002');
insert into TEST_EMPLOYEE values('6',3,'wudde','23000.00','003');
insert into TEST_EMPLOYEE values('7',3,'joker','21000.00','003');
commit;

1. Query all subordinates

select 
	EMP_ID,LEAD_ID,EMP_NAME,prior EMP_NAME as lead_name,SALARY
from TEST_EMPLOYEE
start with LEAD_ID=1
connect by prior EMP_ID = LEAD_ID;

 

2. Query all subordinates including yourself

select 
	EMP_ID,LEAD_ID,EMP_NAME,prior EMP_NAME as lead_name,SALARY
from TEST_EMPLOYEE
start with EMP_ID=1
connect by prior EMP_ID = LEAD_ID;

 

3. Query all superiors including yourself

select 
	EMP_ID,LEAD_ID,EMP_NAME,SALARY
from TEST_EMPLOYEE 
start with EMP_ID=6
connect by prior LEAD_ID = EMP_ID;

 

4. Query the same level personnel of your direct superior, but not including your direct superior

with t as (
	select
		TEST_EMPLOYEE.*,prior EMP_NAME,level le
	from TEST_EMPLOYEE 
	start with LEAD_ID = 0
	connect by LEAD_ID=prior EMP_ID
	)
select 
	t.*
from t 
left join t tt 
	on tt.EMP_ID = 6
where t.le = (tt.le-1)
	and t.EMP_ID <> tt.LEAD_ID;

 

5. Query own peers, but do not include themselves

with t as (
	select 
		TEST_EMPLOYEE.*,prior EMP_NAME,level le
	from TEST_EMPLOYEE 
	start with LEAD_ID=0
	connect by LEAD_ID= prior EMP_ID
	)
select 
	t.*
from t t
left join t tt 
	on tt.EMP_ID = 5
where t.le = tt.le 
	and t.EMP_ID <> tt.EMP_ID;	

 

6. Use of level pseudo column, format level

select 
	lpad(' ',level*2,' ') || EMP_NAME as name,EMP_ID,LEAD_ID,SALARY,level
from TEST_EMPLOYEE
start with LEAD_ID = 0
connect by prior EMP_ID = LEAD_ID

 

7. Connect by root

select 
	connect_by_root EMP_NAME,EMP_NAME,LEAD_ID,SALARY
from TEST_EMPLOYEE  
where DEPT_NO='002'
start with LEAD_ID=1
connect by prior EMP_ID = LEAD_ID;

 

Insert one piece of data, and form a circular row with another piece of data with EMP? Id = 7

insert into TEST_EMPLOYEE values(3,7,'joker_cycle','21000.00','003');
commit;

 

8. Query lower level (data with cycle)

select 
	EMP_ID,EMP_NAME,LEAD_ID,SALARY,connect_by_iscycle as cycle --Whether the dimension is circular
from TEST_EMPLOYEE 
start with LEAD_ID=3
connect by nocycle prior EMP_ID = LEAD_ID;

 

9. -- query whether a leaf node exists (i.e. whether you have lower level nodes, 0 for no, 1 for yes)

select 
	EMP_ID,EMP_NAME,LEAD_ID,SALARY,connect_by_isleaf
from TEST_EMPLOYEE
start with LEAD_ID=0
connect by nocycle prior EMP_ID=LEAD_ID;

 

 

34 original articles published, 5 praised, 2141 visited
Private letter follow

Posted on Mon, 13 Jan 2020 00:18:48 -0800 by amiller099