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

with a as(
    select exam_id,
    avg(timestampdiff(minute,start_time,submit_time)) as avg_time,
    avg(score) as avg_score
    from exam_record 
    group by exam_id
)
select emp_info.emp_id,emp_info.emp_level,tag
from exam_record  join a using(exam_id) join examination_info using(exam_id) join emp_info using(emp_id)
where timestampdiff(minute,start_time,submit_time)<avg_time and score>avg_score and emp_level<7
order by emp_info.emp_id,examination_info.exam_id;