题解 | 完成员工考核试卷突出的非领导员工

select 
    emp_id,
    emp_level,
    exam_tag
from 
    (select
        n.emp_id,
        n.emp_level,
        r.score,
        i.exam_id,
        i.tag as exam_tag,
        timestampdiff(second,r.start_time,r.submit_time) as time,
        avg(timestampdiff(second,r.start_time,r.submit_time)) over (partition by i.tag) as avg_time,
        avg(score) over (partition by i.tag) as avg_score
    from
        exam_record r 
        join examination_info i on r.exam_id=i.exam_id
        join emp_info n on r.emp_id=n.emp_id
    ) t 
where 
    t.emp_level<7
    and t.time<t.avg_time
    and t.score>t.avg_score
order by
    emp_id asc,
    exam_id asc

先用一个子查询给每一行打上平均分和平均时间的标签，然后在外层判断条件