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

with yxs as
(select exam_id,avg(score) score,avg(TIMESTAMPDIFF(MINUTE,start_time,submit_time)) time from exam_record group by exam_id)
select a.emp_id,c.emp_level,d.tag exam_tag from exam_record a JOIN yxs b ON a.exam_id = b.exam_id
join emp_info c on a.emp_id=c.emp_id 
join examination_info  d on d.exam_id =a.exam_id 
where a.score>=b.score and TIMESTAMPDIFF(MINUTE,a.start_time,a.submit_time)<=b.time and c.emp_level<7 order by a.emp_id

yxs 查出平均分和平均用时

下面把三个表和yxs表关联系来，把条件一加，直接出锅