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

#需要满足3个条件： 作答时间< avg(同类作答时间) ；   分数>avg(同类分数)； emp_level < 7

# 先构建临时表，第一个生成两类的平均时间，第二个生成两类的平均分数
with avg_time_table as (
    select exam_id,avg(timestampdiff(minute, start_time, submit_time)) as avg_time
    from exam_record 
    group by exam_id),
     avg_score_table as (
    select exam_id,avg(score) as avg_score
    from exam_record 
    group by exam_id),
    new_record as (
        select emp_id, exam_id,timestampdiff(minute, start_time, submit_time) as time,score
        from exam_record)

select nr.emp_id, 
       ei.emp_level,
       exi.tag
from new_record nr
join avg_time_table att on nr.exam_id = att.exam_id
join avg_score_table ast on nr.exam_id = ast.exam_id
join emp_info ei on ei.emp_id = nr.emp_id
join examination_info exi on exi.exam_id = nr.exam_id
where nr.time < att.avg_time and nr.score > ast.avg_score and ei.emp_level<7
order by nr.emp_id, nr.exam_id