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

with a1 as (
    select exam_id,avg(timestampdiff(second,start_time,submit_time)/60) as avg_exam_time,avg(score) as avg_exam_score
    from exam_record
    group by exam_id
)

select emp_id,emp_level,tag as exam_tag
from exam_record c 
left join emp_info a 
using(emp_id)
left join examination_info b 
using(exam_id)
left join a1 
using(exam_id)
where score > avg_exam_score and timestampdiff(second,start_time,submit_time)/60 < avg_exam_time and emp_level < 7
order by emp_id,exam_id