如何让员工和经理人在一起 [英] How to get the employees with their managers
本文介绍了如何让员工和经理人在一起的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我希望输出看起来像的样子:
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
这就是我得到的:
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.
这是桌子:
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行.
推荐答案
这是经典的自连接,请尝试以下操作:
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
如果要包括没有经理的总裁,则可以使用Oracle语法中的 outer 联接代替 inner 联接>
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:
SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e, emp m
WHERE e.mgr = m.empno(+)
或使用ANSI SQL语法:
Or in ANSI SQL syntax:
SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e
LEFT OUTER JOIN emp m
ON e.mgr = m.empno
这篇关于如何让员工和经理人在一起的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文