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

select
    t1.emp_id,
    t1.emp_level,
    t.tag
from
(select
    e.exam_id,
    e.tag,
    avg(er.score) as avg_score,
    avg(timestampdiff(minute,er.start_time,er.submit_time)) as avg_time
from
    examination_info e
join
    exam_record er on e.exam_id = er.exam_id
group by
    e.exam_id,e.tag) t
join
    (
        select
            er.exam_id,
            e.emp_id,
            e.emp_level,
            timestampdiff(minute,er.start_time,er.submit_time) as timediff,
            er.score
        from
            emp_info e
        join
            exam_record er on e.emp_id = er.emp_id
        where
            e.emp_level < 7 
    ) t1 on t.exam_id = t1.exam_id
where
    t1.timediff<t.avg_time and t1.score>t.avg_score
order by
    t1.emp_id,t1.exam_id