MySQL 分组取前n条的方法比较

背景

有时候我们需要根据表里的分类来获取前N条数据，比如根据课程来查询每科成绩的top10分数，这时候有几种解决方案

解决方案

1. 使用Union 将每科成绩的top10人员以及成绩 使用 Union 连接起来
2. 使用group by 语句，根据成绩分组，然后按照排序规则（成绩倒序）取出成绩表的id，截取前10个
3. 使用where子句，子查询跟主查询中的成绩比较，10>count(*)作为条件，只返回10条数据

实际sql

以下是一些sql语句，留作备忘

• 使用union方式，会导致sql过大，如果分类不超过一定数量，可以考虑

(select a.category_main_id,a.lesson_id,a.lesson_name,ifnull(b.buy_count,0) buy_count,a.create_time from lesson a
left join lesson_count b on a.lesson_id=b.lesson_id and a.agency_id=b.agency_id and a.app_id=b.app_id
where a.agency_id=2 and a.app_id=1 and a.category_main_id in(857) 
order by b.buy_count desc,a.create_time desc
limit 0,8)
union
(select a.category_main_id,a.lesson_id,a.lesson_name,ifnull(b.buy_count,0) buy_count,a.create_time from lesson a
left join lesson_count b on a.lesson_id=b.lesson_id and a.agency_id=b.agency_id and a.app_id=b.app_id
where a.agency_id=2 and a.app_id=1 and a.category_main_id in(858) 
order by b.buy_count desc,a.create_time desc 
limit 0,8)
union
(select a.category_main_id,a.lesson_id,a.lesson_name,ifnull(b.buy_count,0) buy_count,a.create_time from lesson a
left join lesson_count b on a.lesson_id=b.lesson_id and a.agency_id=b.agency_id and a.app_id=b.app_id
where a.agency_id=2 and a.app_id=1 and a.category_main_id in(859) 
order by b.buy_count desc,a.create_time desc 
limit 0,8);

• 使用group by 和 group_concat 实现，先获取id，再根据id获取详细数据

select a.category_main_id,
substring_index(group_concat(a.lesson_id order by b.buy_count desc,a.create_time desc,a.lesson_id desc),',',8) as lesson_ids
 from lesson a
left join lesson_count b on a.lesson_id=b.lesson_id and a.agency_id=b.agency_id and a.app_id=b.app_id
where a.agency_id=2 and a.app_id=1 and a.category_main_id in(857,858,859) 
group by a.category_main_id;

select * from lesson where id in(20005,20004,20006,20003,20002,20001,7750,6423,
20038,20039,20042,20043,20037,20044,20045,20046,
20048,20053,20051,20052,20061,20054,20049,20055);

• 根据where子句查询出排序的前10条，注意排序字段的顺序，通过concat()方法来确保排序条件的顺序

select a.category_main_id,a.lesson_id,a.lesson_name,ifnull(b.buy_count,0) buy_count,a.create_time from lesson a
left join lesson_count b on a.lesson_id=b.lesson_id and a.agency_id=b.agency_id and a.app_id=b.app_id
where a.agency_id=2 and a.app_id=1 and a.category_main_id in(857,858,859) and 8>(
select count(*) from lesson a1
left join lesson_count b1 on a1.lesson_id=b1.lesson_id and a1.agency_id=b1.agency_id and a1.app_id=b1.app_id
where a1.agency_id=2 and a1.app_id=1 and a1.category_main_id in(857,858,859) 
and a1.category_main_id=a.category_main_id and a1.agency_id=a.agency_id and a1.app_id=a.app_id  and 
concat(ifnull(b1.buy_count,0),a1.create_time,a1.lesson_id) > concat(ifnull(b.buy_count,0),a.create_time,a.lesson_id)) 
order by a.category_main_id desc, b.buy_count desc,a.create_time desc,a.lesson_id desc;

实际比较结果，第一种方案最优，第二种次之，最后一种最差
结果如下图所示