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

select
    ein.emp_id emp_id,
    emp_level,
    tag
from
    test.examination_info ei
    join test.exam_record er on ei.exam_id = er.exam_id
    join test.emp_info ein on ein.emp_id = er.emp_id
    join (
        select
            er.exam_id,
            round(sum(score) / count(score), 3) 平均分,
            round(
                avg(timestampdiff (second, start_time, submit_time)) / 60,
                1
            ) 平均用时
        from
            test.examination_info ei
            join test.exam_record er on ei.exam_id = er.exam_id
            join test.emp_info ein on ein.emp_id = er.emp_id
        group by
            exam_id
    ) a on ei.exam_id = a.exam_id
where
    emp_level < 7
    and score > 平均分
    and timestampdiff (second, start_time, submit_time) / 60 < 平均用时
order by
    ein.emp_id asc