您将如何在 SQL 查询中显示员工表中的经理姓名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8510112/
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
How would you display the manager name from employee table in an SQL query?
提问by Joel
I have an employee table that displays the employee number, name, and manager's employee number. I'm trying to create a query that displays the emp name, emp#, manager name and mgr#.
我有一个员工表，显示员工编号、姓名和经理的员工编号。我正在尝试创建一个显示 emp 名称、emp#、经理名称和 mgr# 的查询。
The table looks like this:
该表如下所示：
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT - 17-NOV-81 5000 - 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 - 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 - 10
7566 JONES MANAGER 7839 02-APR-81 2975 - 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 - 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 - 20
Three of the columns are pretty simple since they are in the table but how would I add the manager name when all I have is the employee number of their manager?
其中三列非常简单，因为它们在表中，但是当我只有他们经理的员工编号时，我该如何添加经理姓名？
This is what I have so far that only displays the 3 colums
这是我到目前为止只显示 3 列的内容
select ename as "Employee", empno as "Emp#", mgr as "Mgr#" from emp;
Any help would be appreciated
任何帮助，将不胜感激
回答by Jake Feasel
select
emp.ename as "Employee",
emp.empno as "Emp#",
emp.mgr as "Mgr#",
m.ename as "Manager"
from
emp
LEFT OUTER JOIN emp m ON
emp.mgr = m.empno
回答by bimal
Try this:
尝试这个：
select e.ename ,e.sal, (select s.grade from salgrade s where e.sal between s.losal and s.hisal) empgrade ,e2.ename"mgr", e2.sal mgrsal,
(select s.grade from salgrade s where e2.sal between s.losal and s.hisal) mgrgrade from emp e,emp e2
where e.mgr=e2.empno(+)
回答by PAWAN k
SELECT DISTINCT E.ENAME,E.job
FROM EMP E
JOIN EMP Y
ON E.EMPNO=Y.MGR;
This will give you distinct employee name who are managers.
这将为您提供不同的员工姓名，他们是经理。
Or:
或者：
SELECT E.ENAME,E.job
FROM EMP E
JOIN EMP Y
ON E.EMPNO=Y.MGR;
Or:
或者：
SELECT E.ENAME,E.job
FROM EMP E, EMP Y
WHERE
E.EMPNO=Y.MGR;
回答by TreyE
select
e.ename as "Employee",
e.empno as "Emp#",
e.mgr as "Mgr#",
m.ename as "MgrName"
from emp e
join emp m on e.mgr = m.empno
回答by Nageswara Rao Orsu
select e.ename as "Employee", e.empno as "Emp#", e.mgr as "Mgr#", m.ename as "MgrName" from emp e , emp m on e.mgr = m.empno
回答by Prasath
Try this query
试试这个查询
SELECT E2.EMPID,E2.EMPNAME,E1.EMPNAME
AS MGRNAME
FROM employee E1
INNER JOIN employee E2 ON E1.Empid=E2.ManagerId
回答by Bunny
I tried it is working just fine.
我试过它工作得很好。
SELECT a.empno EmpID,a.empname EmpName, a.mgrid MgrID,(select empname from employee where empno = a.mgrid) MgrName
from employee a, employee b
where a.empno = b.empno order by a.empno asc;
回答by srinivasan pg
SELECT (CASE
WHEN e1.manager_id IS NULL THEN
e1.last_name ||' IS THE PRESIDENT'
ELSE
e2.last_name || ' IS THE MANAGER OF '||e1.last_name
END)"HIERARCHY" FROM hr.employees e1, hr.employees e2 WHERE e1.manager_id = e2.employee_id(+) ORDER BY e1.manager_id NULLS FIRST
回答by dinesh
Select e.ename empname , m.ename managername,e.empno empno ,m.mgr managerno from emp e, mgr m
where m.empno=e.mgr
