#思路:关键在于排序的规则,先按分数最大值、再按最低分数的大值、最后按uid大值进行排序,所以要先准备这些数值; #①两表连接,根据tag和uid分组聚合,求出每类tag下不同uid的max(score)、min(score),形成表tb1; #②基于表tb1,用row_number()排序,形成表tb2,注意排序规则; #③基于tb2,选出排名前三的数据即可。 select tag, uid, r from( select tag, uid, row_number()over(partition by tag order by max_s desc, min_s desc, uid desc) r from (select tag, uid, max(score) max_s, min(score) min_s from exam_record left join examination_info using(exam_id) group by tag, uid) as tb1 ) as tb2 where r<=3