如何在SQL查询中显示雇员表中的经理姓名？

问题描述：

我有一张员工表，显示员工编号，姓名和经理的员工编号。我试图创建一个显示emp名称，emp＃，经理姓名和mgr＃的查询。如何在SQL查询中显示雇员表中的经理姓名？

表看起来是这样的：

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

三列的都是非常简单的，因为它们是在表中，但如何将添加当我只有他们的经理的员工号经理的名字吗？

这是我到目前为止只显示3个colums

select ename as "Employee", empno as "Emp#", mgr as "Mgr#" from emp;

任何帮助，将不胜感激

答

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

答

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

答

试试这个：

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(+)

答

SELECT DISTINCT E.ENAME,E.job 
    FROM EMP E 
    JOIN EMP Y 
    ON E.EMPNO=Y.MGR;

这将给你不同的员工姓名谁是经理。

或者：

SELECT E.ENAME,E.job 
    FROM EMP E 
    JOIN EMP Y 
     ON E.EMPNO=Y.MGR;

或者：

SELECT E.ENAME,E.job 
    FROM EMP E, EMP Y 
    WHERE 
    E.EMPNO=Y.MGR;

答

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

通常的计算工作，我们只是想要的数据 - 未装饰。用户界面和装饰应该留给一个单独的层面 - 而且您不应该处理或修饰空洞！在做所有这些事情时，你的答案也错过了所有其他（有用的）列。 – 2013-07-10 09:41:07

答

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

答

我想它工作得很好。

答

尝试此查询

SELECT E2.EMPID,E2.EMPNAME,E1.EMPNAME 
AS MGRNAME 
FROM employee E1 
    INNER JOIN employee E2 ON E1.Empid=E2.ManagerId

答

Select e.ename empname , m.ename managername,e.empno empno ,m.mgr managerno from emp e, mgr m 
where m.empno=e.mgr

如何在SQL查询中显示雇员表中的经理姓名？

相关推荐