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

#求各类试卷平均成绩、用时
with a as(
 select ei.exam_id,ei.tag,avg(timestampdiff(second,er.start_time,er.submit_time)/60) as avg_time,
    avg(er.score) as avg_score
    from exam_record er
    join examination_info ei
    on er.exam_id = ei.exam_id
    group by ei.exam_id,ei.tag)

select e.emp_id,e.emp_level,ei1.tag
from emp_info e
join exam_record er1 on e.emp_id = er1.emp_id
join examination_info ei1 on er1.exam_id = ei1.exam_id
JOIN a ON ei1.exam_id = a.exam_id 
where er1.score>a.avg_score
and timestampdiff(second,er1.start_time,er1.submit_time)/60 < a.avg_time
and e.emp_level<7

先写一个CTE计算平均用时与成绩，然后写查询加上where条件，使用写的CTE

注意写的查询还要和CTE连接一遍