MySQL講義第27講——select 查詢之自連線查詢

顧名思義，自連線查詢就是一張表和自己進行連線。就是把一張表看成兩張表，使用別名進行區分。

一、資料準備

建立一張員工（emp）表，每個員工都有一個部門領導（leader），資料如下：

mysql> select * from emp;
+------+-----------+--------+---------+--------+
| e_id | e_name    | gender | salary  | leader |
+------+-----------+--------+---------+--------+
| 1101 | 張美華    | 女     | 5000.00 |   1104 |
| 1102 | 王濤      | 男     | 5200.00 |   1104 |
| 1103 | 張學有    | 男     | 4700.00 |   1104 |
| 1104 | 劉得華    | 男     | 5200.00 |   1104 |
| 1105 | 董雯花    | 女     | 5900.00 |   1104 |
| 1106 | 宋族營    | 男     | 6500.00 |   1104 |
| 2201 | 李霜江    | 男     | 7200.00 |   2202 |
| 2202 | 樑美麗    | 女     | 6400.00 |   2202 |
| 2203 | 王大強    | 男     | 6100.00 |   2202 |
| 3301 | 張美華    | 女     | 7800.00 |   1104 |
| 3302 | 趙紫龍    | 男     | 6900.00 |   1104 |
| 3303 | 諸葛量    | 男     | 9200.00 |   1104 |
| 3304 | 曹夢德    | 男     | 8400.00 |   1104 |
+------+-----------+--------+---------+--------+
13 rows in set (0.00 sec)

二、對自連線的理解

為了更好地理解自連線，根據 emp 表建立一個和 emp 中的資料完全相同的檢視，檢視名稱為 leader。

mysql> CREATE VIEW leader
    -> AS
    -> SELECT
    -> *
    -> FROM 
    -> emp;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from leader;
+------+-----------+--------+---------+--------+
| e_id | e_name    | gender | salary  | leader |
+------+-----------+--------+---------+--------+
| 1101 | 張美華    | 女     | 5000.00 |   1104 |
| 1102 | 王濤      | 男     | 5200.00 |   1104 |
| 1103 | 張學有    | 男     | 4700.00 |   1104 |
| 1104 | 劉得華    | 男     | 5200.00 |   1104 |
| 1105 | 董雯花    | 女     | 5900.00 |   1104 |
| 1106 | 宋族營    | 男     | 6500.00 |   1104 |
| 2201 | 李霜江    | 男     | 7200.00 |   2202 |
| 2202 | 樑美麗    | 女     | 6400.00 |   2202 |
| 2203 | 王大強    | 男     | 6100.00 |   2202 |
| 3301 | 張美華    | 女     | 7800.00 |   1104 |
| 3302 | 趙紫龍    | 男     | 6900.00 |   1104 |
| 3303 | 諸葛量    | 男     | 9200.00 |   1104 |
| 3304 | 曹夢德    | 男     | 8400.00 |   1104 |
+------+-----------+--------+---------+--------+
13 rows in set (0.00 sec)

然後根據 emp 表和檢視 leader 建立連線，查詢結果如下：

mysql> SELECT 
    -> e.e_id,
    -> e.e_name emp_name,
    -> e.gender,
    -> e.salary,
    -> l.leader leader_id,
    -> l.e_name leader_name
    -> FROM
    -> emp e JOIN leader l ON e.leader = l.e_id;
+------+-----------+--------+---------+-----------+-------------+
| e_id | emp_name  | gender | salary  | leader_id | leader_name |
+------+-----------+--------+---------+-----------+-------------+
| 1101 | 張美華    | 女     | 5000.00 |      1104 | 劉得華      |
| 1102 | 王濤      | 男     | 5200.00 |      1104 | 劉得華      |
| 1103 | 張學有    | 男     | 4700.00 |      1104 | 劉得華      |
| 1104 | 劉得華    | 男     | 5200.00 |      1104 | 劉得華      |
| 1105 | 董雯花    | 女     | 5900.00 |      1104 | 劉得華      |
| 1106 | 宋族營    | 男     | 6500.00 |      1104 | 劉得華      |
| 2201 | 李霜江    | 男     | 7200.00 |      2202 | 樑美麗      |
| 2202 | 樑美麗    | 女     | 6400.00 |      2202 | 樑美麗      |
| 2203 | 王大強    | 男     | 6100.00 |      2202 | 樑美麗      |
| 3301 | 張美華    | 女     | 7800.00 |      1104 | 劉得華      |
| 3302 | 趙紫龍    | 男     | 6900.00 |      1104 | 劉得華      |
| 3303 | 諸葛量    | 男     | 9200.00 |      1104 | 劉得華      |
| 3304 | 曹夢德    | 男     | 8400.00 |      1104 | 劉得華      |
+------+-----------+--------+---------+-----------+-------------+
13 rows in set (0.00 sec)

三、把以上的查詢用自連線表示

以上基於 emp 表和 leader 檢視的連線要轉換為自連線，只需要把檢視換成 emp 表，並指定一個別名即可，程式碼如下：

mysql> SELECT
    -> e.e_id,
    -> e.e_name emp_name,
    -> e.gender,
    -> e.salary,
    -> l.leader leader_id,
    -> l.e_name leader_name
    -> FROM
    -> emp e JOIN emp l ON e.leader = l.e_id;
+------+-----------+--------+---------+-----------+-------------+
| e_id | emp_name  | gender | salary  | leader_id | leader_name |
+------+-----------+--------+---------+-----------+-------------+
| 1101 | 張美華    | 女     | 5000.00 |      1104 | 劉得華      |
| 1102 | 王濤      | 男     | 5200.00 |      1104 | 劉得華      |
| 1103 | 張學有    | 男     | 4700.00 |      1104 | 劉得華      |
| 1104 | 劉得華    | 男     | 5200.00 |      1104 | 劉得華      |
| 1105 | 董雯花    | 女     | 5900.00 |      1104 | 劉得華      |
| 1106 | 宋族營    | 男     | 6500.00 |      1104 | 劉得華      |
| 2201 | 李霜江    | 男     | 7200.00 |      2202 | 樑美麗      |
| 2202 | 樑美麗    | 女     | 6400.00 |      2202 | 樑美麗      |
| 2203 | 王大強    | 男     | 6100.00 |      2202 | 樑美麗      |
| 3301 | 張美華    | 女     | 7800.00 |      1104 | 劉得華      |
| 3302 | 趙紫龍    | 男     | 6900.00 |      1104 | 劉得華      |
| 3303 | 諸葛量    | 男     | 9200.00 |      1104 | 劉得華      |
| 3304 | 曹夢德    | 男     | 8400.00 |      1104 | 劉得華      |
+------+-----------+--------+---------+-----------+-------------+
13 rows in set (0.00 sec)