在使用mysql 的时候我们经常会遇到查询某个列表里面的不重复数据,或者根据某个字段找出最适合的那条数据。我想很多人肯定都知道,使用单表的唯一查询用:distinct多表的唯一查询用:group by。但是在使用中,我们往往会遇到很多坑,接下来我就说说我遇到的那些坑。
首先我们创建一张表来看看 distinct查询
SELECT DISTINCT NAME FROM `student`
这个时候我们已经看到了 mysql给我们过滤了重复的name 张三这个字段,但是我们光要name通常是没有什么用的,所以我要个id。这个时候加上ID再查一遍。
SELECT DISTINCT NAME,id FROM `student`
但是我们发现结果并没有过滤掉 张三这是为什么啦,这是因为加上id之后,他的过滤就会变成(name,id)两个同时相等才会过滤掉。每加一个字段,他的过滤内容就会多加一个,只有全部都相等的时候才会过滤掉。如果我们想得到一个列表,如果两个人名字相等我们只取id最大的那个,应该怎么做啦,很简单,我们可以要使用GROUP BY。
SELECT NAME,id FROM `student` GROUP BY NAME
好了,你是不是感觉很简单,以前我也是这样用的,可是我现在要告诉你,这种写法是错的。因为我们并没有指定要显示哪个ID。id 1 和6都是张三。这等于相当于mysql给我们自己选择了,在一些特殊环境下面这样写会报错的。所以我们要给mysql加一些函数,要明确的告诉他,我们到底需要什么。比如:姓名相同取最大的ID
SELECT NAME,MAX(id) FROM `student` GROUP BY NAME
当然有最大的就会有最小的。我们也可以显示两个ID。
SELECT NAME,GROUP_CONCAT(id) FROM `student` GROUP BY NAME
默认是以逗号分隔,当然我们也可以改分隔符号。
我们现在是两个字段的单表查,如果是多个字段多表查数据,我要显示这几个表特定的一些字段改怎么办啦。
比如我现在有分数表,和课程表,我想要取出所有学生的姓名,分数和课程。并且去掉重复的学生姓名 id最大的那位:
SELECT b.`id`,a.`course`,b.`name`,c.`score` FROM `student` b LEFT JOIN `student_score` c
ON b.`id`=c.`student_id` LEFT JOIN `score` a ON a.`id`=c.`score_id` GROUP BY b.`name`
这样一看是不是感觉自己写对了,并且很正常,但是我想说,这是错的。因为如果name相同,你并没有指定需要显示最大的或者最小的,或者总数,或者合并在一起的那个字段。而是让mysql自己去选择。这在一些模式下,是不允许通过的。而且,我们这样分组之后,会缺失很多数据。所以我们最好的写法应该是。
SELECT b.`id`,a.`course`,b.`name`,c.`score` FROM `student` b LEFT JOIN `student_score` c
ON b.`id`=c.`student_id` LEFT JOIN `score` a ON a.`id`=c.`score_id` WHERE b.id IN(
SELECT MAX(id) FROM `student` GROUP BY NAME)