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

select ei.emp_id, emp_level,tag as exam_tag
from emp_info ei
         join (select emp_id, a.exam_id
               from exam_record er,
                    (select exam_id, avg(timestampdiff(second, start_time, submit_time)) as t, avg(score) as s
                     from exam_record
                     group by exam_id) a
               where er.score > a.s
                 and timestampdiff(second, start_time, submit_time) < a.t
                 and er.exam_id = a.exam_id) b on b.emp_id = ei.emp_id
        join examination_info  e on e.exam_id=b.exam_id
where emp_level < 7
order by ei.emp_id, b.exam_id;