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 条件中,这样结果才能不多不少,刚刚好。
到此这篇关于MySQL联表查询基本操作之left-join常见坑的文章就介绍到这了,更多相关MySQL联表查询left-join内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!
相关文章
- 这篇文章主要给大家介绍了关于inner join和left join之间区别的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2021-01-16
- 数据库中JOIN操作的实现主要有三种:嵌套循环连接(Nested Loop Join),归并连接(Merge Join)和散列连接或者哈稀连接(Hash Join)。其中嵌套循环连接又视情况又有两种变形:块嵌套循环连接和索引嵌套循环连接。...2015-12-14
- 下面小编就为大家带来一篇Mysql inner join on的用法实例(必看)。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧...2017-04-03
- 本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据。对mysql中的join感兴趣的朋友一起学习吧...2017-04-26
- 这篇文章主要介绍了Mysql join操作的相关资料,需要的朋友可以参考下...2017-05-19
- 这篇文章主要介绍了MySQL中join语句的基本使用及其字段对性能的影响,举了实例来观测join所作用的不同字段字符集编码所造成的性能差异,需要的朋友可以参考下...2015-12-18
SQL Server 使用join all优化 or 查询速度
一个老项目,加载列表奇慢,超过10秒钟, 主要涉及两个表, user表,procuts表。因为涉及多层代理,使用site字段保存目录...2020-12-08- 这篇文章主要给大家介绍了关于MySQL联表查询基本操作之left-join的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用MySQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...2020-05-18
- 这篇文章主要介绍了Mysql join联表及id自增实例解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-09-10
- 这篇文章主要介绍了c++11中关于std::thread的join详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-04-25
JavaScript中split与join函数的进阶使用技巧
这篇文章主要介绍了JavaScript中split与join函数的进阶使用技巧,split和join通常被用来操作数组和字符串之间的转换,需要的朋友可以参考下...2016-05-05详解mysql 使用left join添加where条件的问题分析
这篇文章主要介绍了详解mysql 使用left join添加where条件的问题分析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2021-02-08- 这篇文章主要介绍了Python中join()函数多种操作代码实例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-04-27
- 这篇文章主要介绍了Java jpa外连接查询join案例详解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-09-06
- 这篇文章主要给大家介绍了关于MYSQL数据库基础之Join操作原理的相关资料,连接(join)查询是将两个查询的结果以“横向对接”的方式合并起来的结果,需要的朋友可以参考下...2021-07-05
- 这篇文章主要介绍了laravel中Join语法以及使用Join多个条件,文中不仅介绍了join用法而且讲述了多种方法很详细,有感兴趣的同学可以学习下...2021-03-19
C#中使用Join与GroupJoin将两个集合进行关联与分组
这篇文章主要介绍了C#中使用Join与GroupJoin将两个集合进行关联与分组,文中分别对Join和GroupJoin的用法进行详细说明,需要的朋友可以参考下...2020-06-25c# String扩展 让你在PadLeft和PadRight时不再受单双字节问题困扰
这篇文章主要介绍了c# String扩展 让你在PadLeft和PadRight时不再受单双字节问题困扰,需要的朋友可以参考下...2020-06-25C#多线程之Thread中Thread.Join()函数用法分析
这篇文章主要介绍了C#多线程之Thread中Thread.Join()函数用法,实例分析了Thread.Join()方法的原理与使用技巧,非常具有实用价值,需要的朋友可以参考下...2020-06-25mysql高效查询left join和group by(加索引)
这篇文章主要给大家介绍了关于mysql高效查询left join和group by,这个的前提是加了索引,以及如何在MySQL高效的join3个表的相关资料,需要的朋友可以参考下...2021-06-11