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

1mrurvl1 于 2022-09-18 发布在 Java

关注(0)|答案(11)|浏览(213)

以下是我的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 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

我如何列出经理的名字？*我的内部联接是否正确？

sql-server

来源：https://stackoverflow.com/questions/15678587/list-all-employees-names-and-their-managers-by-manager-name-using-an-inner-join

11条答案

按热度按时间

v440hwme1#

将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;

赞(0）回复(0）举报 2022-09-18

xiozqbni2#

您的查询已关闭，您需要使用mgr和empid连接

on e1.mgr = e2.empid

因此，完整的查询是：

select e1.ename Emp,
  e2.eName Mgr
from employees e1
inner join employees e2
  on e1.mgr = e2.empid

请参阅带有演示的SQL小提琴

如果要返回所有行，包括没有经理的行，则可以将其更改为LEFT JOIN(例如总裁)：

select e1.ename Emp,
  e2.eName Mgr
from employees e1
left join employees e2
  on e1.mgr = e2.empid

请参阅带有演示的SQL小提琴

样例数据中的总裁将为经理返回null值，因为他们没有经理。

赞(0）回复(0）举报 2022-09-18

ltskdhd13#

您在联接处有一个错误的ON子句，这是有效的：

inner join Employees m on e.mgr = m.EmpId;

mgr列引用EmpId列。

演示

赞(0）回复(0）举报 2022-09-18

i5desfxk4#

不，正确的联接是：

inner join Employees m on e.mgr = m.EmpID;

您需要将当前员工的ManagerID与经理的EmployeeID进行匹配。不能使用经理的ManagerID。

更新

正如安德烈·戈尔德耶夫所指出的：
您还需要将m.Ename添加到SELECT查询中，才能在结果中获得经理的名称。否则，您只会得到管理员ID。

赞(0）回复(0）举报 2022-09-18

6bc51xsx5#

select a.empno,a.ename,a.job,a.mgr,B.empno,B.ename as MGR_name, B.job as MGR_JOB from 
    emp a, emp B where a.mgr=B.empno ;

赞(0）回复(0）举报 2022-09-18

xriantvc6#

SELECT DISTINCT e.Ename AS Employee, 
    m.mgr AS reports_to, 
    m.Ename AS manager 
FROM Employees e, Employees m 
WHERE e.mgr=m.EmpID;

赞(0）回复(0）举报 2022-09-18

x7rlezfr7#

select e.ename as Employee, m.ename as Manager
from emp e, emp m
where e.mgr = m.empno

如果要获取所有记录的结果(无论它们是否向任何人报告)，请在第二个表的名称后附加(+

select e.ename as Employee, m.ename as Manager
from emp e, emp m
where e.mgr = m.empno(+)

赞(0）回复(0）举报 2022-09-18

jk9hmnmh8#

Select e.lastname as employee ,m.lastname as manager
  from employees e,employees m
 where e.managerid=m.employyid(+)

赞(0）回复(0）举报 2022-09-18

0pizxfdo9#

有三个表格--股票(账户：ID，ISIN)和债券(账户：ID，ISIN)。第三个表Securities(Coulmns：ID，ISIN)包含来自股票和债券表的所有数据。编写SQL查询以验证以下内容：(1)证券表应包含来自股票和债券表的所有数据。(2)证券表不应包含除权益和债券表中存在的数据以外的任何数据

赞(0）回复(0）举报 2022-09-18

tez616oj10#

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;

赞(0）回复(0）举报 2022-09-18

lzfw57am11#

问题：DISPLAY员工姓名、入职日期、经理姓名和经理入职日期。ANS：-SELECT e1.ename emp，e1.hiredate，e2.eName Manager，e2.hiredate from emp e1，emp e2，其中e1.mgr=e2.empno

赞(0）回复(0）举报 2022-09-18