I want to do a SQL query, find out my subordinates subordinates who are
Found, for example, general manager of the Buddha, the Buddha management subordinates tang's monk, tang's monk management of sun wukong, the Buddha is input, and then you can find out tang's monk and sun wukong,
This is the SQL

LeaderId is inside the boss id, check according to the
This is I write some good content, but found out the result is not the only, there will be multiple duplicate values,,,

Which great god look good, please, thank you first

CodePudding user response:

LeaderId is the last of a value

CodePudding user response:

B. SELECT * FROM the test. The student A
JOIN the test. Student B
ON A.S tudentID=B.L eaderId
WHERE A.S tudentName='Buddha'
UNION ALL
SELECT c. * FROM the test. The student A
JOIN the test. Student B
ON A.S tudentID=B.L eaderId
JOIN the test. Student C
ON B.S tudentID=C.L eaderId
WHERE A.S tudentName='Buddha';

CodePudding user response:

Typical ORACLE tree query, I wrote a about, baidu yourself,
SELECT *
FROM the name of the table T1
START WITH T1. P_ID=? - set the root node
CONNECT BY the PRIOR T1. PKID=T1. P_ID - set loop condition
AND LEVEL=2 - set according to the basic

CodePudding user response:

If it is more than 8, 0 CTE of direct reference manual writing,

 
WITH RECURSIVE employee_paths (id, name, path) AS 
(
SELECT id, name, CAST (id AS CHAR (200)) 
The FROM employees 
WHERE manager_id IS NULL 
UNION ALL 
The SELECT e.i d, e.n ame, CONCAT (ep) path, ', ', e.i d) 
The FROM employee_paths AS ep JOIN employees AS e 
ON ep. Id=e.m anager_id 
) 
SELECT * FROM employee_paths ORDER BY path; 

CodePudding user response:

Don't consider performance select * from table 1 left the join table 1 on Anderson, d=b.l eaderId left the join table 1 on b.i d=c.l eaderId three layers

CodePudding user response:

Laoge said the above, you can use the query tree to solve
Select the id as "id",
The name as "text",
Pid as "pid",
The PRIOR (name) as "pName",
CONNECT_BY_ISLEAF as "isLeaf
"The from employees t
Connect by the prior id=pid - from top to bottom search method
Start with pid='1'
The order siblings by pid, id