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

with standard as (
    select exam_id, avg(timestampdiff(MINUTE,start_time,submit_time)) as avg_time, avg(score) as avg_score
    from exam_record
    group by exam_id
)
select e.emp_id, emp_level, tag as exam_tag
from emp_info e join exam_record er on e.emp_id=er.emp_id join examination_info ei on er.exam_id=ei.exam_id join standard s on er.exam_id=s.exam_id
where emp_level<7 and timestampdiff(MINUTE,start_time,submit_time)<avg_time and score>avg_score
order by e.emp_id asc, er.exam_id asc;