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

with
    temp as (
        select
            exam_id,
            round(
                avg(
                    timestampdiff(second, start_time, submit_time) / 60
                ),
                1
            ) as avg_time,
            avg(score) as avg_score
        from
            exam_record
        group by
            exam_id
    )

首先，先计算出两个考试的平均考试用时和平均分数，计算用了多少秒 再除以60可以得到带小数的分钟表示（其实不除也行，反正最后不用表示出来），建立一个临时表。

select
    er.emp_id,
    emp_level,
    tag as exam_tag
from
    exam_record er
    left join emp_info ei on er.emp_id = ei.emp_id
    left join examination_info ex on er.exam_id = ex.exam_id
    left join temp on er.exam_id = temp.exam_id
where
    timestampdiff(second, start_time, submit_time) / 60 < avg_time
    and score > avg_score
    and emp_level < 7;

然后再把三个表和临时表结合到一起，最后按照条件选出比平均分大，比平均用时少，等级<7的就可以了