SELECT tag,uid,ranking FROM (SELECT tag,uid, row_number()over(partition by tag order by max_score desc,min_score desc,uid desc) as ranking FROM (SELECT tag,uid,MAX(score) as max_score,MIN(score) as min_score FROM exam_record e1 LEFT JOIN examination_info e2 ON e1.exam_id=e2.exam_id GROUP BY uid,tag)...