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

select emp_id,
emp_level,
tag as exam_tag
from
(select
*,
timestampdiff(minute,start_time,submit_time) as t
from exam_record
left join examination_info
using(exam_id)
left join emp_info
using(emp_id)
left join
(select exam_id,
avg(t) as atime,
avg(score) as ascore
from
(select *,
timestampdiff(minute,start_time,submit_time) as t
from exam_record
left join examination_info
using(exam_id)
left join emp_info
using(emp_id)) a
group by exam_id
order by exam_id) b
using(exam_id)) c
where emp_level < 7 and t < atime and score > ascore
order by emp_id,exam_id