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

select emp_id,emp_level,tag as exam_tag
from (select emp_id,exam_id,emp_level,
case 
  when score > s2 and minute < s1 then '优秀'
  else '不合格'
end as situation
from (select emp_id,exam_id,emp_level,avg(timestampdiff(second,start_time,submit_time)/60)over(partition by exam_id) as s1,avg(score)over(partition by exam_id) as s2,timestampdiff(second,start_time,submit_time)/60 as minute,score
from exam_record join emp_info using(emp_id)
group by emp_id,exam_id,score,start_time, submit_time) as temp1
where emp_level < 7) as temp2  join examination_info using(exam_id)
where situation = '优秀'
order by emp_id,emp_level