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

 -- Level1：得到每类试卷的平均用时和平均得分
-- level2：对比每个员工 作答用时与同类平均时长，试卷得分与同类试卷平均分数，并select
with
    avg_situation as (
        select
            eni.tag,
            avg(score) as avg_score,
            avg(timediff (submit_time, start_time)) as avg_time
        from
            emp_info as ei
            inner join exam_record as er on ei.emp_id = er.emp_id
            inner join examination_info as eni on eni.exam_id = er.exam_id
        group by
            eni.tag
    )
select
    er.emp_id,
    emp_level,
    tag as exam_tag
from
    emp_info as ei
    inner join exam_record as er on ei.emp_id = er.emp_id
    inner join examination_info as eni on eni.exam_id = er.exam_id
where
    (
        ei.emp_level < 7
        and tag = '企业文化'
        and timediff (submit_time, start_time) < (
            select
                avg_time
            from
                avg_situation
            where
                tag = '企业文化'
        )
        and score > (
            select
                avg_score
            from
                avg_situation
            where
                tag = '企业文化'
        )
    )or(
        ei.emp_level < 7
        and tag = '技术水平'
        and timediff (submit_time, start_time) < (
            select
                avg_time
            from
                avg_situation
            where
                tag = '技术水平'
        )
        and score > (
            select
                avg_score
            from
                avg_situation
            where
                tag = '技术水平'
        )
    )

这个办法属于比较笨的办法，就是老老实实算出各类卷子的平均分和平均时间，然后作为标量子查询在where中作为条件与员工的分数和用时分别比较。

想到了另一个更好的方法，就是创建一个表，在eni表基础上加两个变量（平均分和平均时间）即可，然后与原表连接，这样就不需要使用标量子查询，也不需要在where中分别设置tag='技术水平'和tag='企业文化'来进行对应类别的比较了