首先是窗口函数的知识:
ROW_NUMBER()函数可以理解为排序号,不考虑并列;
RANK()函数也为排号,考虑并列,并列之后的按照实际序号来;
dense_rank()同样是排号,考虑并列,并列之后按下一个名次来。
排序方面就是根据最大分数,然后最小分数,然后uid排序,右连接exam_record表group by一下来去重
select
p1.tag,
p1.uid
,p1.ranking
from (
SELECT
distinct uid as uid,
tag,
row_number() over(partition by tag
ORDER BY max(score) desc,min(score) desc ,uid desc ) AS ranking
FROM
examination_info e
RIGHT JOIN
exam_record r
ON e.exam_id = r.exam_id
group by
uid,tag
) AS p1
where p1.ranking < 4;