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

select em.emp_id emp_id,
em.emp_level emp_level,
info.tag exam_tag
from emp_info em join exam_record re on em.emp_id=re.emp_id join examination_info info on re.exam_id=info.exam_id join(select exam_id,
 AVG(TIMESTAMPDIFF(MINUTE, start_time, submit_time)) AS avg_diff_time,
AVG(score) AS avg_score
from exam_record
group by exam_id) ae on re.exam_id=ae.exam_id
where em.emp_level<7 and re.score>ae.avg_score and TIMESTAMPDIFF(MINUTE, start_time, submit_time)<ae.avg_diff_time

四表联结：三表+派生表，通过exam_id相连，相当于已经得到了各表关于不同考试类别的分组

派生表用于计算用于比较的值

最后统一在主表中进行where筛选

并且此题不要求按考试类别输出，只是输出每个优秀的人即可