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

with
    d as (
        select
            exam_id,
            avg(timestampdiff (second, start_time, submit_time)) as time_avg,
            avg(score) as score_avg
        from
            exam_record
        group by
            exam_id
    ),
    e as (
        select
            emp_id,
            exam_id,
            avg(timestampdiff (second, start_time, submit_time)) as time_avg1,
            avg(score) as score_avg1
        from
            exam_record
        group by
            exam_id,
            emp_id
    )
        select
            e.emp_id,
            a.emp_level,
            b.tag as exam_tag
        from
            e e
            left join d d on e.exam_id = d.exam_id
            left join examination_info b on b.exam_id = e.exam_id
            left join emp_info a on a.emp_id=e.emp_id
        where
            e.time_avg1 < d.time_avg
            and e.score_avg1 > d.score_avg
            and   a.emp_level<7