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

select
    emp_id,
    emp_level,
    exam_tag
from
    (
        SELECT
            emp_id,
            emp_level,
            exam_id,
            tag exam_tag
        from
            (
                SELECT
                    emp_id,
                    emp_level,
                    exam_id,
                    score,
                    tag,
                    考试分钟,
                    avg(考试分钟) over (
                        partition by
                            tag
                    ) as 同类平均考试分钟,
                    avg(score) over (
                        partition by
                            tag
                    ) 同类平均分数
                from
                    (
                        select
                            A.emp_id,
                            A.exam_id,
                            A.score,
                            B.tag,
                            timestampdiff(minute, start_time, submit_time) as 考试分钟,
                            C.emp_level
                        from
                            exam_record A
                            left join examination_info B ON A.exam_id = B.exam_id
                            left join emp_info C ON A.emp_id = C.emp_id
                    ) as A
            ) as A
        where
            考试分钟 < 同类平均考试分钟
            and score > 同类平均分数
            and emp_level < 7
        order by
            emp_id asc,
            exam_id asc
    ) as A