Some time ago, because of the replacement of the OA of the company, some recursive queries of the upper and lower levels were needed, but no way was found. I found the following information on the Internet, and I probably have a number in my heart.

Create 2 tables first

CREATE TABLE dept(  
deptno INT PRIMARY KEY,  
dname VARCHAR(20),  
loc VARCHAR(20), 
SJdeptno INT 
)  

INSERT INTO dept VALUES (1,'Yingsan factory','Li Jun','')
INSERT INTO dept VALUES (10,'Manufacturing division','pony',1)  
INSERT INTO dept VALUES (11,'R & D department','Xiao Ming',1)  
INSERT INTO dept VALUES (21,'Technology Department','Wang Hu',10)  
INSERT INTO dept VALUES (22,'Quality control department','Zhao Min',10)  
INSERT INTO dept VALUES (23,'Software Department','Wu mutual',11)  
INSERT INTO dept VALUES (24,'Hardware Department','Tommy',11) 

CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20) NOT NULL,


insert into emp values(7369,'Li Jun','CLERK',7902,'1980-12-17',1640,NULL,1);  
insert into emp values(7499,'pony','SALESMAN',7698,'1981-2-20',11400,300,10);  
insert into emp values(7521,'Xiao Ming','SALESMAN',7698,'1981-2-22',5200,500,11);  
insert into emp values(7566,'Wang Hu','MANAGER',7839,'1981-4-2',7015,NULL,21);  
insert into emp values(7654,'Zhao Min','SALESMAN',7698,'1981-9-28',5200,1400,22);  
insert into emp values(7698,'Wu mutual','MANAGER',7839,'1981-5-1',5900,NULL,23);  
insert into emp values(7782,'Tommy','MANAGER',7839,'1981-6-9',2470,NULL,24); 

Deptno in emp table and deptno in dept table are corresponding relationships. We want to query the Department hierarchy of an employee

WITH B1 AS(
SELECT deptno,dname,dname First level Department,''Secondary Department,'' Third level Department,'' Level 4 Department,'' Level 5 Department 
FROM dept WHERE deptno=1 ),  /* deptno=1 As root node, this is the starting point for recursive queries */ 

B2 AS(
SELECT B.deptno,B.dname,B1.First level Department,B.dname Secondary Department,'' Third level Department,'' Level 4 Department,'' Level 5 Department 
FROM B1 JOIN dept B ON B.SJdeptno=B1.deptno ), /* Make a join query between the B1 table and the dept table (b) to get the B2 table */

B3 AS(
SELECT B.deptno,B.dname,B2.First level Department,B2.Secondary Department,B.dname Third level Department,'' Level 4 Department,'' Level 5 Department 
FROM B2 JOIN dept B ON B.SJdeptno=B2.deptno ), /* Make the above table B2 and the dept table (b) as the join query to get table B3 */

B AS(
SELECT * FROM B1
UNION ALL
SELECT * FROM B2
UNION ALL
SELECT * FROM B3)
select * from B  /* Joint query of B1, B2 and B3 */ 

The results of B1, B2 and B3 queries are easy to understand.

WITH B1 AS(
SELECT deptno,dname,dname First level Department,''Secondary Department,'' Third level Department,'' Level 4 Department,'' Level 5 Department FROM dept WHERE deptno=1 ),

B2 AS(
SELECT B.deptno,B.dname,B1.First level Department,B.dname Secondary Department,'' Third level Department,'' Level 4 Department,'' Level 5 Department 
FROM B1 JOIN dept B ON B.SJdeptno=B1.deptno ),

B3 AS(
SELECT B.deptno,B.dname,B2.First level Department,B2.Secondary Department,B.dname Third level Department,'' Level 4 Department,'' Level 5 Department 
FROM B2 JOIN dept B ON B.SJdeptno=B2.deptno ),

B AS(
SELECT * FROM B1
UNION ALL
SELECT * FROM B2
UNION ALL
SELECT * FROM B3)

select ename,First level Department,Secondary Department,Third level Department,Level 4 Department,Level 5 Department from B JOIN emp ON emp.DEPTNO=B.deptno

Query employee's organizational structure with a connection query

In this way, the organizational structure of employees is very clear.