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

with lsb as (select exam_id as examid, avg(minute_diff) as avg_time, avg(score) as avg_score
from (select emp_id,  exam_id, TIMESTAMPDIFF(MINUTE,start_time, submit_time) AS minute_diff, score
from exam_record where emp_id not in (select
emp_id from emp_info where emp_level >= 7)) t
group by exam_id)

select t1.emp_id , ei.emp_level, t1.exam_tag from 
(select emp_id,  if(exam_id  = 9001,'企业文化' , '技术水平') as exam_tag
from exam_record er join lsb on er.exam_id = lsb.examid  where er.emp_id not in (select
emp_id from emp_info where emp_level >= 7) and TIMESTAMPDIFF(MINUTE,start_time, submit_time) < avg_time and score > avg_score) t1 
join emp_info ei on t1.emp_id = ei.emp_id