如何让员工与他们的经理在一起

This is what I want the output to look like:

这就是我想要输出的样子:

Employee   Emp# Manager Mgr# 
   BLAKE   7698 KING    7839 
   CLARK   7782 KING    7839 
   JONES   7566 KING    7839 
   MARTIN  7654 BLAKE   7698 
   ALLEN   7499 BLAKE   7698 
   TURNER  7844 BLAKE   7698 
   JAMES   7900 BLAKE   7698 
   WARD    7521 BLAKE   7698 
   FORD    7902 JONES   7566 
   SMITH   7369 FORD    7902 
   SCOTT   7788 JONES   7566 
   ADAMS   7876 SCOTT   7788 
   MILLER  7934 CLARK   7782

Here's what I got:

这是我得到的:

 SQL> SELECT ename, empno, (SELECT ename FROM EMP WHERE empno = mgr)AS MANAGER, mgr from emp order by empno;

 ENAME           EMPNO MANAGER           MGR
 ---------- ---------- ---------- ----------
 SMITH            7369                  7902
 ALLEN            7499                  7698
 WARD             7521                  7698
 JONES            7566                  7839
 MARTIN           7654                  7698
 BLAKE            7698                  7839
 CLARK            7782                  7839
 SCOTT            7788                  7566
 KING             7839
 TURNER           7844                  7698
 ADAMS            7876                  7788

 ENAME           EMPNO MANAGER           MGR
 ---------- ---------- ---------- ----------
 JAMES            7900                  7698
 FORD             7902                  7566
 MILLER           7934                  7782

I can't find why the manager field is blank.

我找不到管理员字段为空的原因。

Here's the table:

这是表格:

 SQL> select empno, ename, job,deptno, mgr from emp;

 EMPNO ENAME      JOB           DEPTNO        MGR
 ---------- ---------- --------- ---------- ----------
  7839 KING       PRESIDENT         10
  7698 BLAKE      MANAGER           30       7839
  7782 CLARK      MANAGER           10       7839
  7566 JONES      MANAGER           20       7839
  7654 MARTIN     SALESMAN          30       7698
  7499 ALLEN      SALESMAN          30       7698
  7844 TURNER     SALESMAN          30       7698
  7900 JAMES      CLERK             30       7698
  7521 WARD       SALESMAN          30       7698
  7902 FORD       ANALYST           20       7566
  7369 SMITH      CLERK             20       7902

 EMPNO ENAME      JOB           DEPTNO        MGR
 ---------- ---------- --------- ---------- ----------
  7788 SCOTT      ANALYST           20       7566
  7876 ADAMS      CLERK             20       7788
  7934 MILLER     CLERK             10       7782

14 rows selected.

选择了14行。

5 个解决方案

#1

This is a classic self-join, try the following:

这是一个经典的自我加入,请尝试以下方法:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e, emp m
WHERE e.mgr = m.empno

And if you want to include the president which has no manager then instead of an inner join use an outer join in Oracle syntax:

如果您想要包含没有管理器的总统,那么在Oracle语法中使用外部联接而不是内部联接:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e, emp m
WHERE e.mgr = m.empno(+)

Or in ANSI SQL syntax:

或者在ANSI SQL语法中:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e
    LEFT OUTER JOIN emp m
        ON e.mgr = m.empno

#2

(SELECT ename FROM EMP WHERE empno = mgr)

There are no records in EMP that meet this criteria.

EMP中没有符合此标准的记录。

You need to self-join to get this relation.

你需要自我加入来获得这种关系。

SELECT e.ename AS Employee, e.empno, m.ename AS Manager, m.empno
FROM EMP AS e LEFT OUTER JOIN EMP AS m
ON e.mgr =m.empno;

EDIT:

The answer you selected will not list your president because it's an inner join. I'm thinking you'll be back when you discover your output isn't what your (I suspect) homework assignment required. Here's the actual test case:

您选择的答案不会列出您的总统,因为它是内部联接。当你发现你的输出不是你(我怀疑的)所需的家庭作业时,我想你会回来的。这是实际的测试用例:

> select * from emp;

 empno | ename |    job    | deptno | mgr  
-------+-------+-----------+--------+------
  7839 | king  | president |     10 |     
  7698 | blake | manager   |     30 | 7839
(2 rows)

> SELECT e.ename employee, e.empno, m.ename manager, m.empno
FROM emp AS e LEFT OUTER JOIN emp AS m
ON e.mgr =m.empno;

 employee | empno | manager | empno 
----------+-------+---------+-------
 king     |  7839 |         |      
 blake    |  7698 | king    |  7839
(2 rows)

The difference is that an outer join returns all the rows. An inner join will produce the following:

不同之处在于外连接返回所有行。内部联接将产生以下内容:

> SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM emp e, emp m
WHERE e.mgr = m.empno;

 ename | empno | manager | mgr  
-------+-------+---------+------
 blake |  7698 | king    | 7839
(1 row)

#3

Perhaps your subquery (SELECT ename FROM EMP WHERE empno = mgr) thinks, give me the employee records that are their own managers! (i.e., where the empno of a row is the same as the mgr of the same row.)

也许您的子查询(SELECT ename FROM EMP WHERE empno = mgr)认为,给我的员工记录是他们自己的经理! (即,行的empno与同一行的mgr相同。)

have you considered perhaps rewriting this to use an inner (self) join? (I'm asking, becuase i'm not even sure if the following will work or not.)

您是否考虑过重写此内容以使用内部(自我)联接? (我问,因为我甚至不确定以下内容是否有效。)

SELECT t1.ename, t1.empno, t2.ename as MANAGER, t1.mgr
from emp as t1
inner join emp t2 ON t1.mgr = t2.empno
order by t1.empno;

#4

TRY THIS

SELECT E.ename,E.empno,ISNULL(E.ename,'NO MANAGER') AS MANAGER FROM emp e
INNER JOIN emp M
ON  M.empno=E.empno

Instaed of subquery use self join

Instaed of subquery使用自联接

#5

You could have just changed your query to:

您可能刚刚将查询更改为:

SELECT ename, empno, (SELECT ename FROM EMP WHERE empno = e.mgr)AS MANAGER, mgr 
from emp e 
order by empno;

This would tell the engine that for the inner emp table, empno should be matched with mgr column from the outer table. 如何让员工与他们的经理在一起

这将告诉引擎对于内部emp表,empno应该与外部表中的mgr列匹配。

#1