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

select t1.emp_id, t4.emp_level, t3.tag
from exam_record t1
join (
    select exam_id, avg(score) as avg_score, avg(time_to_sec(timediff(submit_time, start_time))/60) as avg_time
    from exam_record
    group by exam_id
) t2
join examination_info t3
join emp_info t4
on t1.exam_id = t2.exam_id and t1.exam_id = t3.exam_id and t1.emp_id = t4.emp_id
where t4.emp_level < 7
and time_to_sec(timediff(submit_time, start_time))/60 < t2.avg_time
and score > t2.avg_score