还显示分配给员工和经理的计算机模型，如何做

Question

显示分配了计算机及其经理也分配了计算机的员工的 ID、姓名、经理 ID 和经理姓名。还显示分配给员工和经理的计算机型号。 预期输出：ID ENAME MGRID MGRNAME E_MODEL M_MODEL 5 阿亚兹·穆罕默德 1 詹姆斯·波特·埃奇·沃斯特罗

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

select e1.id ID,e1.ename ENAME,e1.manager MGRID,e2.ename MGRNAME    
,c1.model E_MODEL ,c2.model M_MODEL    
 from employee e1 inner join employee e2    
 on e1.manager =e2.id    
 inner join computer c1 on e1.compid = c1.compid     
 inner join computer c2 on e2.compid=c2.compid      

Answer 3

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

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