使用内部联接按经理姓名列出所有员工的姓名及其经理

Question

以下是我的 CREATE TABLE 脚本：

 create table EMPLOYEES
    (EmpID    char(4)         unique Not null,
     Ename    varchar(10),
     Job      varchar(9),
     MGR      char(4),
     Hiredate date,
     Salary   decimal(7,2),
     Comm     decimal(7,2),
     DeptNo   char(2)         not null,
         Primary key(EmpID),
         Foreign key(DeptNo) REFERENCES DEPARTMENTS(DeptNo));

以下是我的 INSERT 脚本：

 insert into EMPLOYEES values (7839,'King','President',null,'17-Nov-11',5000,null,10);
insert into EMPLOYEES values (7698,'Blake','Manager',7839,'01-May-11',2850,null,30);
insert into EMPLOYEES values (7782,'Clark','Manager',7839,'02-Jun-11',2450,null,10);
insert into EMPLOYEES values (7566,'Jones','Manager',7839,'02-Apr-11',2975,null,20);
insert into EMPLOYEES values (7654,'Martin','Salesman',7698,'28-Feb-12',1250,1400,30);
insert into EMPLOYEES values (7499,'Allen','Salesman',7698,'20-Feb-11',1600,300,30);
insert into EMPLOYEES values (7844,'Turner','Salesman',7698,'08-Sep-11',1500,0,30);
insert into EMPLOYEES values (7900,'James','Clerk',7698,'22-Feb-12',950,null,30);
insert into EMPLOYEES values (7521,'Ward','Salesman',7698,'22-Feb-12',1250,500,30);
insert into EMPLOYEES values (7902,'Ford','Analyst',7566,'03-Dec-11',3000,null,20);
insert into EMPLOYEES values (7369,'Smith','Clerk',7902,'17-Dec-10',800,null,20);
insert into EMPLOYEES values (7788,'Scott','Analyst',7566,'09-Dec-12',3000,null,20);
insert into EMPLOYEES values (7876,'Adams','Clerk',7788,'12-Jan-10',1100,null,20);
insert into EMPLOYEES values (7934,'Miller','Clerk',7782,'23-Jan-12',1300,null,10);

以下是我的 SELECT 脚本：

 select distinct e.Ename as Employee, m.mgr as reports_to
from EMPLOYEES e
inner join Employees m on e.mgr = m.mgr;

我用相应的经理 ID 获取员工；

 Ford    7566
Scott   7566
Allen   7698
James   7698
Martin  7698
Turner  7698
Ward    7698
Miller  7782
Adams   7788
Blake   7839
Clark   7839
Jones   7839
Smith   7902

我如何同时列出经理姓名？ *我在做正确的内部连接吗？*

原文由 J.S. Orris 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

添加 m.Ename 到您的 SELECT 查询：

 select distinct e.Ename as Employee, m.mgr as reports_to, m.Ename as Manager
from EMPLOYEES e
inner join Employees m on e.mgr = m.EmpID;

原文由 Andrey Gordeev 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

SELECT DISTINCT E.FirstName As "Employee Name",
   M.FirstName AS "Manager"
     FROM Employee E
       INNER JOIN Employee M
         ON E.ID = M.ManagerID
         Order By E.FirstName;

原文由 user19990761 发布，翻译遵循 CC BY-SA 4.0 许可协议