记录MySQL分组查询后如何获取每组的前5条数据(子查询中未去重复数据,不然会出现TOP5没有查询出5条数据)
SELECT
school_name,
team_name,
team_total as tearm_total,substring(team_create_time,1,10) as team_create_time
FROM
(
/*子查询中为去重复数据,不然会出现TOP5没有查询出5条数据*/
select school_name,
team_name,
team_total,team_create_time,semester
from second_team_info GROUP BY school_name,
team_name,
team_total,team_create_time,semester
) t
WHERE
t.team_total is not null and
5>
(
SELECT count( * ) FROM
(
/*子查询中为去重复数据,不然会出现TOP5没有查询出5条数据*/
select school_name,
team_name,
team_total,team_create_time,semester
from second_team_info GROUP BY school_name,
team_name,
team_total,team_create_time,semester
) s WHERE s.school_name = t.school_name AND t.semester = s.semester and s.team_total > t.team_total
)
AND t.semester ='2020-2021-1'
GROUP BY school_name,
team_name,
team_total,team_create_time
ORDER BY
school_name,
team_total DESC