SQL中group by问题
在使用sql语句中,我们常常使用group by加聚合函数来分组并聚合,从而实现某些需求。然而,不正确地使用group by和聚合函数,会带来非常隐晦的问题。
有这样一个需求:对表进行分组后找出用户首次获得最高分数的那条记录的全部信息。
为此,我创建了一张记录用户闯关信息的表,记录了用户在不同课程领域的不同关卡下获得的分数信息:
最开始我觉得很简单,sql是这样写的:
select pl.id, pl.user_id, pl.course_id, pl.pass_id, max(pl.total_score) from pass_log pl group by pl.user_id, pl.course_id, pl.pass_id;
结果是这样的:
大家发现端倪没有?分组和聚合的结果都是对的,但是id却是错的,正确的id应该分别是3、5啊!但是为什么取的是1、4呢?因为它取的是分组后的第一条记录的id!那应该怎么改过来呢?我的想法是先获取分组后的信息和最高分,再和原表进行内关联:
select p.id, p.user_id, p.course_id, p.pass_id, p.total_score from pass_log p join ( select pl.user_id, pl.course_id, pl.pass_id, max(pl.total_score) maxTotalScore from pass_log pl group by pl.user_id, pl.course_id, pl.pass_id) t on p.user_id = t.user_id and p.course_id = t.course_id and p.pass_id = t.pass_id where p.total_score = maxTotalScore group by p.user_id, p.course_id, p.pass_id;
我在最外层又加group by的原因是用户可能在同一个关卡里获得多次最高分,而我只想要首次获得最高分数的那条记录,最后结果是正确的,如图:
后来我想数据查出来的都是错的,为什么mysql不报错呢?这样不是坑我?后来发现还真的会出现报错的情况,执行sql观察:
select @@global.sql_mode;
如果结果中含有ONLY_FULL_GROUP_BY的话,执行我的第一条sql语句就会报错,想要不报错的话就要把pl.id改为any_value(pl.id),但是结果还是取分组后的第一个id,并不是我想要的结果。
其实我最后想表达的是,如果你的group by语句在一个环境下没问题,但是在其它环境下就报异常,那会不会是你group by用错了?