最近遇到一个联表查询的问题。
mysql语句如下:
这是查询文章列表:
SELECT
content.id,
content.fromurl,
content.title,
content.content,
content.doctype,
content.docform,
DATE_FORMAT(
content.createtime,
'%Y-%c-%d %h:%i:%s'
) AS createtime
FROM
content
WHERE content.userid= 19;
这是文章列表联表-用户表 和联表-点赞表
SELECT
content.id,
content.fromurl,
content.title,
content.content,
content.doctype,
content.docform,
DATE_FORMAT(
content.createtime,
'%Y-%c-%d %h:%i:%s'
) AS createtime,
count(docomlike.id) AS likeNum,
menber.username,
menber.nickname
FROM
content
LEFT JOIN docomlike ON docomlike.docid = content.id AND docomlike.commentid = 0
LEFT JOIN menber ON menber.id = content.userid
WHERE content.userid= 19;
他们都用了left join 来联表的,也就是说,结果应该以content表的结果长度为准。
但是,出现了点意外,联表docmlike表后,却只剩下一条数据,得到类似 domcomlike表变成了inner join的效果,但是实际上并不是。。。
domcomlike是点赞表,并不是所有的文章都都被点赞。。但是menber表是用户用户表是每个文章都存在对应的用户的。。。所以问题肯定处在这里。
第一反应觉得是COUNT(docomlike.docid) 返回了null导致的,于是我把语句改为以下:
SELECT
cnt.title,
IFNULL(COUNT(docomlike.docid),0) as likeNum
FROM
(SELECT * FROM content WHERE userid= 19) AS cnt
LEFT JOIN docomlike ON docomlike.docid = cnt.id
IFNULL(COUNT(docomlike.docid),0) as likeNum 的意思是,如果该值为null,就用0替换掉
但是,结果行数依旧有问题,只是likenum变成了0
好吧,到这里我几乎无解了,只能在baidu和谷歌上面搜索类似`mysql 联表 COUNT 导致结果数量不对`这样的关键词
还好功夫不负有心人,最后找到了解决方法是需要对主表进行groupby,虽然目前我还是没法理解或者找到相关文档说明,但是先记录下。
目前的结论是:使用了聚合函数count的查询,其它字段必须增加分组函数
以下文字来源 https://blog.csdn.net/l139955/article/details/71122776
今天遇到了一个sql问题,及理解过程记录下
select a.id as id,
count(b.id)
from A a left join B b on a.id = b.aid
A表可以理解为班级表
B表为学生表,现在用以上语句试图求出每个班级下多少个学生
这个语句在oracle中,是有问题的,会直接报错,因为oracle要求非聚合函数的字段都必须进行分组
但是在mysql中不一样,所以以下分析是针对mysql的
这个语句执行出来貌似mysql自动完成了分组,其实是有细微差别的
当A表有数据时,则很正常
但是当A表为空数据时,执行以上语句,得到以下结果
null,0
从这个结果上看,和分组之后的结果就不一样了,增加分组
select a.id as id,
count(b.id)
from A a left join B b on a.id = b.aid
group by a.id
得到的结果是无记录
分析如下:
其实两个表的关联,可以理解为,两个表的所有列合并在一起,所有数据根据连接条件进行笛卡尔积之后得到的一个大的临时表,之后的所有过滤或其它函数之类的,就针对这个大的临时表进行操作
前者的写法,等同于
select a.id as id,
count(*)
from A a left join B b on a.id = b.aid
其联合后的临时表,其实是一个空表
那么使用count函数后,数据库会生成一个除了count(*)有值,其它字段都为null的结果集,以显示count(*) 的计算结果,所以前者就得到了null,0这样的结果集
而使用分组函数之后,分组函数的特点是,会忽略null值,从而得到正确结果
结论:
为了防止此问题的出现,在写sql时一定要规范,使用了聚合函数的查询,其它字段必须增加分组函数。