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

select
emp_id,emp_level,tag
from
    exam_record
    left join (
        select
            exam_id,
            tag,
            avg(score) as avg_score,
            avg(
                abs(timestampdiff (second, start_time, submit_time))
            ) as avg_time
        from
            exam_record
            left join examination_info using (exam_id)
            left join emp_info using(emp_id)
        # where emp_level>7
        group by
            exam_id,tag
     
    )as t1 using (exam_id)
left join emp_info  using(emp_id)
where
    score > avg_score
    and abs(timestampdiff (second, start_time, submit_time)) < avg_time
    and emp_level<7