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

with tmp as       
(select a.exam_id,b.avg_time,a.avg_score
from(
select exam_id,avg(score) avg_score
from exam_record
group by exam_id
)as a left join(
select exam_id,avg(TIMESTAMPDIFF(MINUTE,start_time,submit_time)) avg_time
from exam_record
group by exam_id)as b on a.exam_id=b.exam_id
)
select t2.emp_id,t1.emp_level,t3.tag
from(
select emp_id,emp_level
from emp_info
where emp_level<7) t1,
(select *,timestampdiff(MINUTE,start_time,submit_time) answer_time
from exam_record
) t2,tmp,examination_info t3
where tmp.exam_id=t2.exam_id and t1.emp_id=t2.emp_id and t2.score>tmp.avg_score and t2.answer_time<tmp.avg_time and t3.exam_id=t2.exam_id
order by t2.emp_id asc,t2.exam_id asc

1. 使用timestampdiff计算出所用的时间，创建一个临时表记录 不同试卷的平均时间和平均分数
2. 多表查询😎