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

select 
t1.emp_id
,t3.emp_level
,t2.tag
from exam_record t1
left join examination_info t2
on t1.exam_id = t2.exam_id
left join emp_info  t3
on t1.emp_id = t3.emp_id
left join (
    select 
    exam_id
    ,avg(score) as avg_score
    ,avg(timestampdiff(minute,start_time,submit_time)) as avg_minute
    from  exam_record 
    group by exam_id
) t4
on t1.exam_id = t4.exam_id
where t1.score >t4.avg_score and timestampdiff(minute,start_time,submit_time) < t4.avg_minute
and t3.emp_level < 7