场景:
举例,有一个用户做题记录的表,
create table test(
id bigint(20) primary key comment '主键id',
userId bigint(20) comment'用户id',
count int(10) comment'做题数量',
cost int(10) comment'完成做题消耗的时间,单位秒'
);
现在表中有5条数据
id | userid | count | cost |
1 | 100 | 6 | 3 |
2 | 101 | 6 | 4 |
3 | 100 | 7 | 5 |
4 | 101 | 5 | 2 |
5 | 102 | 5 | 3 |
需求:
查询出每个用户最多的做题数的数据,并按照做题数量倒序,完成时间正序排列,即最终结果
100 | 7 | 5 |
101 | 6 | 4 |
102 | 5 | 3 |
sql:
方案1(错误):
select userid,max(count) count,cost from test group by userid order by count desc,cost limit 0,10;
结果:
100 | 7 | 3 |
101 | 6 | 4 |
102 | 5 | 3 |
用户 100 做题数量7 对应的耗时却是3,明显不正确
方案2:
select userid,max(count),cost from (
select userid,count,cost from test order by count desc,cost
) as t group by userid order by count desc,cost limit 0,10;
j结果:
100 | 7 | 5 |
101 | 6 | 4 |
102 | 5 | 3 |
符合预期结果
猜测:
方案1查询的时候是按照主键去排序的,接着在做分组(group by)的时候,顺序遍历,因为没有对cost做聚合,所以没有覆盖操作;
方案2查询的时候按照需求先做了排序,接着做分组(group by),由于已经对数据做了排序,所以每个用户的第一条数据就是满足要求的数据。
其他:
方案2 得到的结果虽然满足需求,但是依赖于mysql内部对group by 的处理逻辑,如果服务器做了版本调整,可能就得不到想要的结果了。