mysql按指标各取前五名

SELECT

schoolId, gradeId, classId, studentSequence, studentName, totalScore,

semesterId, semesterName, planId, planName

FROM

(

SELECT

c.schoolId, c.gradeId, c.classId, c.businessKey studentSequence, c.businessName studentName, c.historyCredits totalScore,

p.semesterId, p.semesterName, p.id planId, p.planName,

row_number() over (PARTITION BY c.planId ORDER BY historyCredits DESC ) AS `rank`

FROM

em_credits c LEFT JOIN em_plan p on c.planId = p.id

WHERE

c.businessType = "STUDENT"

AND c.creditsType = "TOTAL"

AND c.schoolId = 226

AND p.semesterId = 91

) ranked_rows

WHERE

`rank` <= 5;