Existing department table and staff table, department table has d_id (department number), d_parentId (superior department number) field, employee table has e_id (employee id), e_did (department number) field, use a query, the query results for two columns,

The first column of the second column
Employee number employees number
Employee number department number
Employee number superior departments number
Employee number on the upper number
...
Department number department number
Department number the superior department number
Department number on the superior department number
...
Superior department number superior department number
The superior department number on the superior department number
...

CodePudding user response:

The basic data and results

CodePudding user response:

 
- provide a train of thought: 
- 1, for a single employee (ygbh_001 bmbh_001) department, it is concluded that all of its superior departments: 
Select 'ygbh_001 d1l, BMBH d2l from BMB start with SJBMBH=' bmbh_001 'connect by SJBMBH=the prior BMBH 
The from BMB. 
- 2, for all employees, can consider to use cursor to implement