学生成绩表: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;
结果如下