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

【场景】：前几名、窗口函数里面的多个排序

【分类】：专用窗口函数、嵌套子查询

分析思路

难点：

1.里面有一个问题时之前没有遇到过的，就是：如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。这种可以求出最大值和最小值列，再通过’ORDER BY max_score desc,min_score desc,uid desc‘表述。

2.另外需要注意where不可以引用select里面as 的变量，如果想用需要重新select

（1）统计每类试卷每个作答用户的最大分数和最小分数

​ [使用]：group by tag,uid

（2）根据最大分数降序，最小分数降序，uid降序规则按照顺序编号

​ [使用]：rank() over (partition by tag ORDER BY max_score desc,min_score desc,uid desc) as ranking

（3）输出每类试卷得分的前3名

​ [条件]：where ranking <= 3

最终结果

select 查询结果 [试卷类型; 用户ID;排名]
from 从哪张表中查询数据[嵌套from子查询]
where 查询条件 [每类试卷得分的前3名]

扩展

前往查看： MySQL 窗口函数

求解代码

方法一

with 子句

with
    main as(
        #统计每类试卷每个作答用户的最大分数和最小分数
        select 
            tag,
            uid,
            max(score) as max_score,
            min(score) as min_score
        from examination_info a, exam_record b
        where a.exam_id = b.exam_id
        group by tag,uid
    ),
    attr as(
        #根据最大分数降序，最小分数降序，uid降序规则按照顺序编号
        select 
            tag as tid,
            uid,
            rank() over (partition by tag ORDER BY max_score desc,min_score desc,uid desc) as ranking
        from main
    )

#输出每类试卷得分的前3名
select *
from attr
where ranking <= 3

方法二

嵌套子查询 + from子查询

select *
from(
    select 
    tag as tid,
    uid,
    rank() 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 examination_info a, exam_record b
        where a.exam_id = b.exam_id
        group by tag,uid
    ) attr
) main
where ranking <= 3