MySQL讲义第27讲——select 查询之自连接查询

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)
©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页