MySQL聯表查詢基本操作之left-join常見的坑

概述

對於中小體量的項目而言，聯表查詢是再常見不過的操作了，尤其是在做報表的時候。然而校對數據的時候，您發現坑了嗎？本篇文章就 mysql 常用聯表查詢復現常見的坑。

基礎環境

建表語句

  DROP TABLE IF EXISTS `role`;  CREATE TABLE `role` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `role_name` VARCHAR(50) DEFAULT NULL COMMENT '角色名',   PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表';      insert into `role` VALUES(1, '管理員');  insert into `role` VALUES(2, '總經理');  insert into `role` VALUES(3, '科長');  insert into `role` VALUES(4, '組長');    DROP TABLE IF EXISTS `user`;  CREATE TABLE `user` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `role_id` int(11) NOT NULL COMMENT '角色id',   `user_name` VARCHAR(50) DEFAULT NULL COMMENT '用戶名',   `sex` int(1) DEFAULT 0 COMMENT '性別',   PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表';    insert into `user` VALUES(1, 1, 'admin', 1);  insert into `user` VALUES(2, 2, '王經理', 1);  insert into `user` VALUES(3, 2, '李經理', 2);  insert into `user` VALUES(4, 2, '張經理', 2);  insert into `user` VALUES(5, 3, '王科長', 1);  insert into `user` VALUES(6, 3, '李科長', 1);  insert into `user` VALUES(7, 3, '呂科長', 2);  insert into `user` VALUES(8, 3, '邢科長', 1);  insert into `user` VALUES(9, 4, '範組長', 2);  insert into `user` VALUES(10, 4, '趙組長', 2);  insert into `user` VALUES(11, 4, '姬組長', 1);

數據如下

  mysql> select * from role;  +----+-----------+  | id | role_name |  +----+-----------+  | 1 | 管理員  |  | 2 | 總經理  |  | 3 | 科長   |  | 4 | 組長   |  +----+-----------+  4 rows in set (0.00 sec)    mysql> select * from user;  +----+---------+-----------+------+  | id | role_id | user_name | sex |  +----+---------+-----------+------+  | 1 |    1 | admin   |  1 |  | 2 |    2 | 王經理  |  1 |  | 3 |    2 | 李經理  |  2 |  | 4 |    2 | 張經理  |  2 |  | 5 |    3 | 王科長  |  1 |  | 6 |    3 | 李科長  |  1 |  | 7 |    3 | 呂科長  |  2 |  | 8 |    3 | 邢科長  |  1 |  | 9 |    4 | 範組長  |  2 |  | 10 |    4 | 趙組長  |  2 |  | 11 |    4 | 姬組長  |  1 |  +----+---------+-----------+------+  11 rows in set (0.00 sec)

基本業務

簡單信息報表: 查詢用戶信息

  mysql> SELECT    -> id,    -> user_name AS '姓名',    -> ( CASE WHEN sex = 1 THEN '男' WHEN sex = 2 THEN '女' ELSE '未知' END ) AS '性別'    -> FROM    -> USER;  +----+-----------+--------+  | id | 姓名   | 性別  |  +----+-----------+--------+  | 1 | admin   | 男   |  | 2 | 王經理  | 男   |  | 3 | 李經理  | 女   |  | 4 | 張經理  | 女   |  | 5 | 王科長  | 男   |  | 6 | 李科長  | 男   |  | 7 | 呂科長  | 女   |  | 8 | 邢科長  | 男   |  | 9 | 範組長  | 女   |  | 10 | 趙組長  | 女   |  | 11 | 姬組長  | 男   |  +----+-----------+--------+

查詢每個角色名稱及對應人員中女性數量

  mysql> SELECT    -> r.id,    -> r.role_name AS role,    -> count( u.sex ) AS sex    -> FROM    -> role r    -> LEFT JOIN USER u ON r.id = u.role_id    -> AND u.sex = 2    -> GROUP BY    -> r.role_name    -> ORDER BY    -> r.id ASC;  +----+-----------+-----+  | id | role   | sex |  +----+-----------+-----+  | 1 | 管理員  |  0 |  | 2 | 總經理  |  2 |  | 3 | 科長   |  1 |  | 4 | 組長   |  2 |  +----+-----------+-----+  4 rows in set (0.00 sec)

假如我們把性別過濾的條件改為 where 操作結果會怎麼樣呢？

  mysql> SELECT    -> r.id,    -> r.role_name AS role,    -> count( u.sex ) AS sex    -> FROM    -> role r    -> LEFT JOIN USER u ON r.id = u.role_id    -> WHERE    -> u.sex = 2    -> GROUP BY    -> r.role_name    -> ORDER BY    -> r.id ASC;  +----+-----------+-----+  | id | role   | sex |  +----+-----------+-----+  | 2 | 總經理  |  2 |  | 3 | 科長   |  1 |  | 4 | 組長   |  2 |  +----+-----------+-----+  3 rows in set (0.00 sec)

這裡可以看到角色數據不完整了。

找出角色為總經理的員工數量

  mysql> SELECT    -> r.id,    -> r.role_name AS role,    -> count( u.sex ) AS sex    -> FROM    -> role r    -> LEFT JOIN USER u ON r.id = u.role_id    -> WHERE    -> r.role_name = '總經理'    -> GROUP BY    -> r.role_name    -> ORDER BY    -> r.id ASC;  +----+-----------+-----+  | id | role   | sex |  +----+-----------+-----+  | 2 | 總經理  |  3 |  +----+-----------+-----+  1 row in set (0.00 sec)

同樣將過濾條件由 where 改為 on

  mysql> SELECT    -> r.id,    -> r.role_name AS role,    -> count( u.sex ) AS sex    -> FROM    -> role r    -> LEFT JOIN USER u ON r.id = u.role_id    -> AND r.role_name = '總經理'    -> GROUP BY    -> r.role_name    -> ORDER BY    -> r.id ASC;  +----+-----------+-----+  | id | role   | sex |  +----+-----------+-----+  | 1 | 管理員  |  0 |  | 2 | 總經理  |  3 |  | 3 | 科長   |  0 |  | 4 | 組長   |  0 |  +----+-----------+-----+  4 rows in set (0.00 sec)

這裡可以看到數據多餘了

總結

在 left join 語句中，左表過濾必須放 where 條件中，右表過濾必須放 on 條件中，這樣結果才能不多不少，剛剛好。