SQL 如何让员工和他们的经理在一起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7451761/
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 to get the employees with their managers
提问by user770022
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 行。
回答by Xint0
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 innerjoin use an outerjoin 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
回答by Brian Roach
(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)
回答by Lambs
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 列匹配。
回答by Pramod Deshmukh
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 的子查询使用自连接
回答by Funka
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 samerow.)
也许你的子查询(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;
