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

#emp_info emp_id
#examination_info exam_id
#exam_record emp_id+exam_id+start_time


#需求 emp_id <同类试卷平均用时 >同类试卷平均分，exam_id emp level＜7

select
a.emp_id
,emp_level
,tag as emp_tag
from
    (select
    emp_id
    ,t1.exam_id
    from 
        exam_record as t1
    left join 
        (select
        exam_id
        ,avg(timestampdiff(minute,start_time,submit_time)) as avg_time
        ,avg(score) as avg_score
        from exam_record 
        where score is not null
        group by 1) t2 -- 试卷作答平均表
    on t1.exam_id=t2.exam_id
    where timestampdiff(minute,start_time,submit_time) < avg_time
    and score>avg_score)a
left join emp_info emi 
on a.emp_id =emi.emp_id
left join examination_info exi
on a.exam_id = exi.exam_id
where emp_level<7
order by emp_id