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

select
    t.emp_id,
    t.emp_level,
    t.tag as exam_tag
from
    (
        SELECT
            exam_record.*,
            examination_info.tag,
            emp_info.emp_level,
            TIMESTAMPDIFF (minute, start_time, submit_time) as kssj,
            avg(score) over (
                partition by
                    exam_id
            ) as avgscore,
            avg(TIMESTAMPDIFF (minute, start_time, submit_time)) over (
                partition by
                    exam_id
            ) as avgkssj,
            if (
                (
                    avg(score) over (
                        partition by
                            exam_id
                    ) < score
                )
                AND (
                    TIMESTAMPDIFF (minute, start_time, submit_time) < avg(TIMESTAMPDIFF (minute, start_time, submit_time)) over (
                        partition by
                            exam_id
                    )
                ),
                1,
                0
            ) as tuchu
        from
            exam_record
            left join examination_info on exam_record.exam_id = examination_info.exam_id
            LEFT JOIN emp_info on exam_record.emp_id = emp_info.emp_id
    ) t
WHERE
    t.emp_level < 7
    and t.tuchu = 1
order by
    t.emp_id,
    exam_id