MySQL联表查询基本操作之left-join常见的坑
概述
对于中小体量的项目而言，联表查询是再常见不过的操作了，尤其是在做报表的时候。然而校对数据的时候，您发现坑了吗？本篇文章就mysql常用联表查询复现常见的坑。
基础环境
建表语句
DROPTABLEIFEXISTS`role`; CREATETABLE`role`( `id`int(11)NOTNULLAUTO_INCREMENT, `role_name`VARCHAR(50)DEFAULTNULLCOMMENT'角色名', PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='角色表'; insertinto`role`VALUES(1,'管理员'); insertinto`role`VALUES(2,'总经理'); insertinto`role`VALUES(3,'科长'); insertinto`role`VALUES(4,'组长'); DROPTABLEIFEXISTS`user`; CREATETABLE`user`( `id`int(11)NOTNULLAUTO_INCREMENT, `role_id`int(11)NOTNULLCOMMENT'角色id', `user_name`VARCHAR(50)DEFAULTNULLCOMMENT'用户名', `sex`int(1)DEFAULT0COMMENT'性别', PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='用户表'; insertinto`user`VALUES(1,1,'admin',1); insertinto`user`VALUES(2,2,'王经理',1); insertinto`user`VALUES(3,2,'李经理',2); insertinto`user`VALUES(4,2,'张经理',2); insertinto`user`VALUES(5,3,'王科长',1); insertinto`user`VALUES(6,3,'李科长',1); insertinto`user`VALUES(7,3,'吕科长',2); insertinto`user`VALUES(8,3,'邢科长',1); insertinto`user`VALUES(9,4,'范组长',2); insertinto`user`VALUES(10,4,'赵组长',2); insertinto`user`VALUES(11,4,'姬组长',1);
数据如下
mysql>select*fromrole; +----+-----------+ |id|role_name| +----+-----------+ |1|管理员| |2|总经理| |3|科长| |4|组长| +----+-----------+ 4rowsinset(0.00sec) mysql>select*fromuser; +----+---------+-----------+------+ |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| +----+---------+-----------+------+ 11rowsinset(0.00sec)
基本业务
简单信息报表:查询用户信息
mysql>SELECT ->id, ->user_nameAS'姓名', ->(CASEWHENsex=1THEN'男'WHENsex=2THEN'女'ELSE'未知'END)AS'性别' ->FROM ->USER; +----+-----------+--------+ |id|姓名|性别| +----+-----------+--------+ |1|admin|男| |2|王经理|男| |3|李经理|女| |4|张经理|女| |5|王科长|男| |6|李科长|男| |7|吕科长|女| |8|邢科长|男| |9|范组长|女| |10|赵组长|女| |11|姬组长|男| +----+-----------+--------+
查询每个角色名称及对应人员中女性数量
mysql>SELECT ->r.id, ->r.role_nameASrole, ->count(u.sex)ASsex ->FROM ->roler ->LEFTJOINUSERuONr.id=u.role_id ->ANDu.sex=2 ->GROUPBY ->r.role_name ->ORDERBY ->r.idASC; +----+-----------+-----+ |id|role|sex| +----+-----------+-----+ |1|管理员|0| |2|总经理|2| |3|科长|1| |4|组长|2| +----+-----------+-----+ 4rowsinset(0.00sec)
假如我们把性别过滤的条件改为where操作结果会怎么样呢？
mysql>SELECT ->r.id, ->r.role_nameASrole, ->count(u.sex)ASsex ->FROM ->roler ->LEFTJOINUSERuONr.id=u.role_id ->WHERE ->u.sex=2 ->GROUPBY ->r.role_name ->ORDERBY ->r.idASC; +----+-----------+-----+ |id|role|sex| +----+-----------+-----+ |2|总经理|2| |3|科长|1| |4|组长|2| +----+-----------+-----+ 3rowsinset(0.00sec)
这里可以看到角色数据不完整了。
找出角色为总经理的员工数量
mysql>SELECT ->r.id, ->r.role_nameASrole, ->count(u.sex)ASsex ->FROM ->roler ->LEFTJOINUSERuONr.id=u.role_id ->WHERE ->r.role_name='总经理' ->GROUPBY ->r.role_name ->ORDERBY ->r.idASC; +----+-----------+-----+ |id|role|sex| +----+-----------+-----+ |2|总经理|3| +----+-----------+-----+ 1rowinset(0.00sec)
同样将过滤条件由where改为on
mysql>SELECT ->r.id, ->r.role_nameASrole, ->count(u.sex)ASsex ->FROM ->roler ->LEFTJOINUSERuONr.id=u.role_id ->ANDr.role_name='总经理' ->GROUPBY ->r.role_name ->ORDERBY ->r.idASC; +----+-----------+-----+ |id|role|sex| +----+-----------+-----+ |1|管理员|0| |2|总经理|3| |3|科长|0| |4|组长|0| +----+-----------+-----+ 4rowsinset(0.00sec)
这里可以看到数据多余了
总结
在leftjoin语句中，左表过滤必须放where条件中，右表过滤必须放on条件中，这样结果才能不多不少，刚刚好。
到此这篇关于MySQL联表查询基本操作之left-join常见坑的文章就介绍到这了,更多相关MySQL联表查询left-join内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！