Oracle之自连接-上司下属

我会带着你远行 2022-05-03 19:26 153阅读 0赞

> 自连接：使用自连接可以将自身表的一个[镜像][Link 1]当作另一个表来对待，从而能够得到一些特殊的数据。

<table> scott的emp表 
 <tbody> 
 <tr> 
 <td> &nbsp; &nbsp;&nbsp; </td> 
 <td> 名称 </td> 
 <td> 类型 </td> 
 <td> 描述 </td> 
 </tr> 
 <tr> 
 <td> 1 </td> 
 <td> EMPNO </td> 
 <td> NUMBER(4) </td> 
 <td> 雇员编号 </td> 
 </tr> 
 <tr> 
 <td> 2 </td> 
 <td> ENAME </td> 
 <td> VARCHAR2(10) </td> 
 <td> 雇员姓名 </td> 
 </tr> 
 <tr> 
 <td> 3 </td> 
 <td> JOB </td> 
 <td> VARCHAR2(9) </td> 
 <td> 雇员职位 </td> 
 </tr> 
 <tr> 
 <td> 4 </td> 
 <td> MGR </td> 
 <td> NUMBER(4) </td> 
 <td> 雇员对应的领导编号，领导也是雇员 </td> 
 </tr> 
 <tr> 
 <td> 5 </td> 
 <td> HIREDATE </td> 
 <td> DATE </td> 
 <td> 雇员的雇佣日期 </td> 
 </tr> 
 <tr> 
 <td> 6 </td> 
 <td> SAL </td> 
 <td> NUMBER(7,2) </td> 
 <td> 基本工资 </td> 
 </tr> 
 <tr> 
 <td> 7 </td> 
 <td> COMM </td> 
 <td> NUMBER(7,2) </td> 
 <td> 奖金 </td> 
 </tr> 
 <tr> 
 <td> 8 </td> 
 <td> DEPTNO </td> 
 <td> NUMBER(2) </td> 
 <td> 雇员所在的部门编号 </td> 
 </tr> 
 </tbody> 
</table>

emp表可以想象成两张表，进行连接

SQL> select * from emp;     [上司表]
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-12月-80            800                    20
          7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
          7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
          7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
          7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
          7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
          7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
          7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
          7839 KING       PRESIDENT            17-11月-81           5000                    10
          7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
          7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
          7900 JAMES      CLERK           7698 03-12月-81            950                    30
          7902 FORD       ANALYST         7566 03-12月-81           3000                    20
          7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
    
    已选择14行。
    
    
    SQL> select * from emp;    [下属表]
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-12月-80            800                    20
          7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
          7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
          7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
          7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
          7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30
          7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
          7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
          7839 KING       PRESIDENT            17-11月-81           5000                    10
          7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
          7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20
    
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
          7900 JAMES      CLERK           7698 03-12月-81            950                    30
          7902 FORD       ANALYST         7566 03-12月-81           3000                    20
          7934 MILLER     CLERK           7782 23-1月 -82           1300                    10
    
    已选择14行。

注意：下属表的MGR（上司编号）字段对应上司表的EMPNO（员工编号）

--------------------

> **简单连接：（所有没有上司、没有下属的都不列出来）**

SQL> select eshang.ename 上司,exia.ename 下属
      2  from emp eshang,emp exia
      3  where eshang.empno=exia.mgr;   //上司表里的员工编号在下属表里是领导(mgr)编号
    
    上司       下属
    ---------- ----------
    JONES      FORD
    JONES      SCOTT
    BLAKE      TURNER
    BLAKE      ALLEN
    BLAKE      WARD
    BLAKE      JAMES
    BLAKE      MARTIN
    CLARK      MILLER
    SCOTT      ADAMS
    KING       BLAKE
    KING       JONES
    
    上司       下属
    ---------- ----------
    KING       CLARK
    FORD       SMITH
    
    已选择13行。

--------------------

> **简单连接等价于内连接：（所有没有上司、没有下属的都不列出来）**

SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang inner join emp exia on eshang.empno=exia.mgr;
    
    
    上司       下属
    ---------- ----------
    FORD       SMITH
    BLAKE      ALLEN
    BLAKE      WARD
    KING       JONES
    BLAKE      MARTIN
    KING       BLAKE
    KING       CLARK
    JONES      SCOTT
    BLAKE      TURNER
    SCOTT      ADAMS
    
    上司       下属
    ---------- ----------
    BLAKE      JAMES
    JONES      FORD
    CLARK      MILLER
    
    已选择13行。

--------------------

> **左外连接：（没有下属也列出来）**

SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang left join emp exia on eshang.empno=exia.mgr;
    
    上司       下属
    ---------- ----------
    SMITH                         //诸如SMITH没有下属
    ALLEN
    WARD
    JONES      FORD
    JONES      SCOTT
    MARTIN
    BLAKE      TURNER
    BLAKE      ALLEN
    BLAKE      WARD
    BLAKE      JAMES
    BLAKE      MARTIN
    
    上司       下属
    ---------- ----------
    CLARK      MILLER
    SCOTT      ADAMS
    KING       BLAKE
    KING       JONES
    KING       CLARK
    TURNER
    ADAMS
    JAMES
    FORD       SMITH
    MILLER
    
    已选择21行。

--------------------

> **右外连接：（没有上司也列出来）**

SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang right join emp exia on eshang.empno=exia.mgr;
    
    上司       下属
    ---------- ----------
    JONES      FORD
    JONES      SCOTT
    BLAKE      JAMES
    BLAKE      TURNER
    BLAKE      MARTIN
    BLAKE      WARD
    BLAKE      ALLEN
    CLARK      MILLER
    SCOTT      ADAMS
    KING       CLARK
    KING       BLAKE
    
    上司       下属
    ---------- ----------
    KING       JONES
    FORD       SMITH
               KING           //KING没有上司
    
    已选择14行。

--------------------

> **完全外连接：（所有没有上司、没有下属的也都列出来）**

SQL> select eshang.ename 上司,exia.ename 下属 from emp eshang full join emp exia on eshang.empno=exia.mgr;
    
    
    上司       下属
    ---------- ----------
    FORD       SMITH
    BLAKE      ALLEN
    BLAKE      WARD
    KING       JONES
    BLAKE      MARTIN
    KING       BLAKE
    KING       CLARK
    JONES      SCOTT
               KING
    BLAKE      TURNER
    SCOTT      ADAMS
    
    上司       下属
    ---------- ----------
    BLAKE      JAMES
    JONES      FORD
    CLARK      MILLER
    TURNER
    WARD
    MARTIN
    ALLEN
    MILLER
    SMITH
    ADAMS
    JAMES
    
    已选择22行。

[Link 1]: https://baike.baidu.com/item/%E9%95%9C%E5%83%8F/1574