题解 | #每类试卷得分前3名#

select tid
        ,uid
        ,ranking
from(
    select tag AS tid
        ,uid
        ,row_number() over(partition by tag order by max(score) desc,min(score) desc, uid desc) AS ranking
    from exam_record er
    inner join examination_info ei on er.exam_id = ei.exam_id
    group by tag,uid
)t
where ranking <= 3
/*更加清晰的写法，先group by得出用户在每类tag下的最高分和最低分，得到表t1，再对表1进行开窗得到表t1
select *
from(
  select uid
  		,tid
  		,row_number() over(partition by tid order by max_score desc,min_score desc,uid desc) AS ranking
  from(
	
	select uid
		  ,tag AS tid
		  ,max(score) AS max_score
		  ,min(score) AS min_score
	from exam_record er
	inner join examination_info ei on er.exam_id = ei.exam_id
	group by uid
			  ,tag
	)t1
  )t2
 where ranking < 3
*/

1. 计算每个用户在每类试卷下的最高分和最低分，group by tag, uid
2. 根据tag，对select出来的tag、uid、最高分和最低分进行开窗，选出每个tag下的最高分，也就是max(score)，如果最高分相等，则根据uid的最低分min(score）中的最大者，使用降序排序。