#思路:关键在于排序的规则,先按分数最大值、再按最低分数的大值、最后按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