还显示分配给员工和经理的计算机型号，以及如何做到这一点

Question

显示被分配了计算机且其经理也被分配了一台计算机的员工的id、姓名、经理id和经理姓名。还显示分配给员工和经理的计算机型号。预期输出：ID ENAME MGRID MGRNAME E_MODEL M_MODEL5 Ayaz Mohammad 1 James Potter Edge Vostro

Answer 1

select
e.id, e.ename, e.mgrid, m.mgrname, e.e_model, m.m_model
from (
select e.id, e.ename, e.manager mgrid, c.model e_model
from employee e, computer c
where e.compid = c.compid
) e, (
select e.id mgrid, e.ename mgrname, c.model m_model
from employee e, computer c
where e.compid = c.compid
) m
where e.mgrid = m.mgrid

Answer 2

您应该考虑尝试自己做作业

CREATE TABLE computers (serial_number,  manufacturer, model) AS
SELECT 'D123',  'Dell', 'laptop' FROM DUAL UNION ALL
SELECT 'D124',  'Dell', 'laptop' FROM DUAL UNION ALL
SELECT 'A1424',  'Apple', 'laptop' FROM DUAL UNION ALL
SELECT 'A1425',  'Apple', 'laptop' FROM DUAL UNION ALL
SELECT 'C1725',  'compaq', 'tower' FROM DUAL UNION ALL
SELECT 'C1726',  'compaq', 'tower' FROM DUAL UNION ALL
SELECT 'C1727',  'compaq', 'tower' FROM DUAL;

CREATE TABLE employees (employee_id, manager_id, first_name, last_name, serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron','D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chase','A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Doris','A1425' FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', 'Evans','C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Frank','C1726' FROM DUAL UNION ALL
SELECT 7, 6, 'Gemma', 'Grace','C1727' FROM DUAL;

select
EMP.EMPLOYEE_ID,
EMP.FIRST_NAME,
EMP.LAST_NAME,
EMP.MANAGER_ID,
M.FIRST_NAME, 
M.LAST_NAME,
EMP.serial_number,
C.manufacturer,
C.model
from
employees emp
JOIN computers c ON emp.serial_number = c.serial_number
LEFT OUTER JOIN employees m ON emp.MANAGER_ID = m.EMPLOYEE_ID
ORDER BY EMP.EMPLOYEE_ID;
EMPLOYEE_ID    FIRST_NAME    LAST_NAME    MANAGER_ID    FIRST_NAME    LAST_NAME    SERIAL_NUMBER    MANUFACTURER    MODEL
1    Alice    Abbot     -      -      -     D123    Dell    laptop
2    Beryl    Baron    1    Alice    Abbot    D124    Dell    laptop
3    Carol    Chase    1    Alice    Abbot    A1424    Apple    laptop
4    Debra    Doris    2    Beryl    Baron    A1425    Apple    laptop
5    Emily    Evans    3    Carol    Chase    C1725    compaq    tower
6    Fiona    Frank    3    Carol    Chase    C1726    compaq    tower
7    Gemma    Grace    6    Fiona    Frank    C1727    compaq    tower