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

with pro_employee as 
(select
    exam_id,
    avg(score) as avg_score,
    avg(timestampdiff(minute,start_time,submit_time)) as avg_minute
from
    exam_record
group by 
    exam_id)
select
distinct
    er.emp_id,
    ei.emp_level,
    eo.tag as exam_tag
from
    exam_record er 
left join
    emp_info ei on er.emp_id = ei.emp_id
left join
    examination_info eo on er.exam_id = eo.exam_id
left join
    pro_employee pe on er.exam_id = pe.exam_id
where 
    ei.emp_level < 7 and er.score > pe.avg_score and timestampdiff(minute,er.start_time,er.submit_time) < pe.avg_minute
order by 
    er.emp_id