SQL 使用内部联接按经理姓名列出所有员工的姓名及其经理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15678587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List all employee's names and their managers by manager name using an inner join
提问by Squ1rr3lz
The following is my CREATE TABLE script:
以下是我的 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));
The following is my INSERT script:
以下是我的插入脚本：
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);
The following is my SELECT script:
以下是我的 SELECT 脚本：
select distinct e.Ename as Employee, m.mgr as reports_to
from EMPLOYEES e
inner join Employees m on e.mgr = m.mgr;
Im getting the employees with their corresponding manager's ID;
我通过相应的经理 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
How do I list the manager name as well?*Am I doing the right inner join?*
我如何列出经理姓名？*我在做正确的内连接吗？*
回答by Andrey Gordeev
Add m.Enameto your SELECTquery:
添加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;
回答by Taryn
Your query is close you need to join using the mgrand the empid
您的查询很接近，您需要使用mgr和加入empid
on e1.mgr = e2.empid
So the full query is:
所以完整的查询是：
select e1.ename Emp,
e2.eName Mgr
from employees e1
inner join employees e2
on e1.mgr = e2.empid
If you want to return all rows including those without a manager then you would change it to a LEFT JOIN(for example the president):
如果您想返回所有行，包括那些没有经理的行，那么您可以将其更改为LEFT JOIN（例如总统）：
select e1.ename Emp,
e2.eName Mgr
from employees e1
left join employees e2
on e1.mgr = e2.empid
The president in your sample data will return a nullvalue for the manager because they do not have a manager.
您的样本数据中的总裁将为null经理返回一个值，因为他们没有经理。
回答by Bazzz
No, the correct join is:
不，正确的连接是：
inner join Employees m on e.mgr = m.EmpID;
You need to match the ManagerID for the current employee with the EmployeeID of the manager. Not with the ManagerID of the manager.
您需要将当前员工的 ManagerID 与经理的 EmployeeID 相匹配。不与经理的 ManagerID。
update
As noted by Andrey Gordeev:
You'd also need to add m.Enameto your SELECTquery in order to get the name of the Manager in your result. Otherwise you'd only get the managerID.
更新
正如 Andrey Gordeev 所指出的：
您还需要添加m.Ename到您的SELECT查询中，以便在您的结果中获得经理的姓名。否则你只会得到managerID。
回答by Tim Schmelter
回答by shiv kumar prasad
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 ;
回答by Mona
There are three tables- Equities(coulmns: ID,ISIN) and Bond(coulmns: ID,ISIN). Third table Securities(coulmns: ID,ISIN) contains all data from Equities and Bond tables. Write SQL queries to validate below: (1) Securities table should contain all the data from Equities and Bonds tables. (2) Securities table should not contain any data other than present in Equities and Bonds tables
共有三个表 - Equities(coulmns: ID,ISIN) 和 Bond(coulmns: ID,ISIN)。第三个表 Securities(coulmns: ID,ISIN) 包含来自 Equities 和 Bond 表的所有数据。编写 SQL 查询以验证以下内容： (1) Securities 表应包含来自 Equities 和 Bonds 表的所有数据。(2) 证券表不应包含股票和债券表以外的任何数据
回答by crazyStart
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;
回答by Ruchi Gupta
select e.ename as Employee, m.ename as Manager
from emp e, emp m
where e.mgr = m.empno
If you want to get the result for all the records (irrespective of whether they report to anyone or not), append (+) on the second table's name
如果要获取所有记录的结果（无论它们是否向任何人报告），请在第二个表的名称上附加 (+)
select e.ename as Employee, m.ename as Manager
from emp e, emp m
where e.mgr = m.empno(+)
回答by Sandeep Desai
Select e.lastname as employee ,m.lastname as manager
from employees e,employees m
where e.managerid=m.employyid(+)
回答by Shubham Jain
question:-.DISPLAY EMPLOYEE NAME , HIS DATE OF JOINING, HIS MANAGER NAME & HIS MANAGER'S DATE OF JOINING. ANS:- select e1.ename Emp,e1.hiredate, e2.eName Mgr,e2.hiredate from emp e1, emp e2 where e1.mgr = e2.empno
问题：-.显示员工姓名、他的加入日期、他的经理姓名和他的经理的加入日期。ANS:- 选择 e1.ename Emp,e1.hiredate, e2.eName Mgr,e2.hiredate from emp e1, emp e2 where e1.mgr = e2.empno
