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

with t_avg_time as (
    select exam_id, avg(timestampdiff(second, start_time, submit_time)) as avg_time
    from exam_record
    group by exam_id
), t_avg_score as (
    select exam_id, avg(score) as avg_score
    from exam_record
    group by exam_id
)
select a.emp_id, emp_level, tag as exam_tag
from (
    select emp_id, exam_id, score,
    timestampdiff(second, start_time, submit_time) as diff 
    from exam_record
) a
join emp_info b on a.emp_id=b.emp_id
join examination_info c on a.exam_id=c.exam_id
join t_avg_time on a.exam_id=t_avg_time.exam_id
join t_avg_score on a.exam_id=t_avg_score.exam_id
where emp_level<7 and a.diff<avg_time and a.score>avg_score
order by a.emp_id, a.exam_id