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

select ei.emp_id,emp_level,tag as exam_tag
from (
    select avg(score) as avg_score ,avg(timestampdiff(minute ,start_time,submit_time)) as avg_time,exam_record.exam_id
    from exam_record join examination_info on exam_record.exam_id=examination_info.exam_id
    join emp_info on exam_record.emp_id=emp_info.emp_id

    group by exam_record.exam_id
     ) as t1 join exam_record on t1.exam_id=exam_record.exam_id
join examination_info e on exam_record.exam_id = e.exam_id
join emp_info ei on exam_record.emp_id = ei.emp_id
where score>avg_score and timestampdiff(minute ,start_time,submit_time)<avg_time and emp_level<7

order by emp_id