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

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