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

select r.emp_id,
emp_level,
t.tag exam_tag
from exam_record r
join emp_info u using (emp_id)
join examination_info e using (exam_id)
join (
    select tag, 
    avg(timestampdiff(second, start_time, submit_time)) avg_time,
    avg(score) avg_score
    from exam_record r
    join examination_info e using (exam_id)
    group by tag
) t
on e.tag=t.tag
where timestampdiff(second, start_time, submit_time)<avg_time
and score>avg_score
and emp_level<7