MySQL联表查询基本操作之left-join常见的坑

吾爱主题阅读：230 2024-04-05 16:21:32 评论：0

概述

对于中小体量的项目而言，联表查询是再常见不过的操作了，尤其是在做报表的时候。然而校对数据的时候，您发现坑了吗？本篇文章就 mysql 常用联表查询复现常见的坑。

基础环境

建表语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 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);

数据如下

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 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)

基本业务

简单信息报表: 查询用户信息

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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 | 姬组长 | 男 | + ----+-----------+--------+

查询每个角色名称及对应人员中女性数量

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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 操作结果会怎么样呢？

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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)

这里可以看到角色数据不完整了。

找出角色为总经理的员工数量

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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)