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

SELECT t2.emp_id , t2.emp_level , tag as exam_tag from (
SELECT emp_info.emp_id,exam_record.exam_id ,  emp_info .emp_level  from emp_info 
left join exam_record on emp_info.emp_id = exam_record .emp_id 
left join (
SELECT exam_id  ,avg(TIMESTAMPDIFF(MINUTE,start_time,submit_time)) as minute ,avg(score) as avgscore from exam_record 
group by exam_id)t1
on exam_record .exam_id = t1.exam_id
where emp_info .emp_level  < 7  and TIMESTAMPDIFF(MINUTE,start_time,submit_time) < minute and score > avgscore
order by exam_id )t2

left join examination_info  on t2.exam_id = examination_info.exam_id