sql - Oracle SQL 组合表信息并创建输出
问题描述
我在 Oracle SQL 开发人员中有两个表,我试图弄清楚如何显示“fname,ssn”和员工对应的经理“fname,snn”。下面是我创建的表格,但我似乎无法让员工信息与他们的后续经理相对应。
员工表
| EMPLOYEE# | FNAME | LNAME | SNN | DEPARTMENT_NAME | JOB_TITLE |
| 1 | jim | box | 11 | accounting | person |
| 2 | james | boer | 22 | IT | manager |
| 3 | tom | jo | 33 | IT | person |
| 4 | tim | quo | 44 | accounting | manager |
部门表
| MANAGER_EMPLOYEE# | DEPARTMENT_NAME |
| 2 | IT |
| 4 | accounting |
期望的输出
| EMPLOYEE NAME | Employee SSN | MANAGER NAME | MANAGER SSN |
| jim box | 11 | james boer | 22 |
| tom jo | 33 | tim quo | 44 |
这就是我到目前为止所拥有的。它只是为了显示员工信息和经理#,因为我不确定如何真正让它链接到经理姓名,然后显示为我想要的输出
select employee#, fname, lname, employee.department_name, manager_employee# from employee left outer join department on employee.employee# = department.manager_employee#
union
select employee#, fname, lname, employee.department_name, manager_employee# from employee right outer join department on employee.employee# = department.manager_employee#;
解决方案
看起来你需要这个:
select
emp.employee#,
emp.fname,
emp.lname,
emp.snn,
emp.department_name,
dep.manager_employee#,
man.fname as manager_fname,
man.lname as manager_lname
from
employee emp
left join department dep
on emp.department_name = dep.department_name
left join employee man
on dep.manager_employee# = man.employee#;