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

select
    emp_info.emp_id,
    emp_level,
    tag exam_tag
from

    (
        select
            ei.exam_id,
            tag,
            avg(score) avg_score,
            avg(timestampdiff (second, start_time, submit_time)) avg_time
        from
            exam_record er
            left join examination_info ei on er.exam_id = ei.exam_id
        group by
            ei.exam_id
    ) tb1 left join exam_record on exam_record.exam_id = tb1.exam_id
    left join emp_info on emp_info.emp_id = exam_record.emp_id
where    
    emp_level < 7
    and score > avg_score
    and timestampdiff (second, start_time, submit_time) < avg_time
order by
    emp_info.emp_id asc,
    exam_record.exam_id asc;