MySQL分组排序查前N条数据

学生成绩表:score

要查询出每个学生考的最好的三次记录

SELECT
	a.*, b.rownum
FROM
	score a
LEFT JOIN (
	SELECT
		id,
		CASE
	WHEN @mid = student THEN
		@ROW :=@ROW + 1
	ELSE
		@ROW := 1
	END rownum,
	@mid := student mid
FROM
	(
		SELECT
			@rownum := 0 ,@mid := '',
			c.*
		FROM
			score c
	) t
ORDER BY
	student,
	score DESC
) b ON b.id = a.id
WHERE
	b.rownum <= 3;

结果如下