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

select
        er.emp_id,
        emp_level,
        tag as exam_tag
from 
        exam_record as er
left join (
    select
        exam_id,
        avg(timestampdiff(second,start_time,submit_time)) as avg_time,
        avg(score) as avg_score
    from 
            exam_record 
    group by 
            exam_id
)a 
on 
        er.exam_id = a.exam_id
left join 
        examination_info as exi
on 
        exi.exam_id = er.exam_id
left join  
        emp_info as ei
on 
        ei.emp_id = er.emp_id
where 
        timestampdiff(second,start_time,submit_time) < avg_time
and 
        score > avg_score
and 
        emp_level < 7
order by 
        er.emp_id