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

with avg_data as (
    #首先计算平均值
    select
        exam_id,
        avg(score) as avg_score,
        avg(timestampdiff(second, start_time, submit_time)) as avg_time
    from exam_record as er 
    group by exam_id
),

emp_only_info as (
    #筛选出非领导员工
    select emp_id, emp_level
    from emp_info
    where emp_level < 7
)
select er.emp_id,eof.emp_level, ei.tag
from emp_only_info as eof
    left join exam_record as er on eof.emp_id = er.emp_id
    left join avg_data as ad on ad.exam_id = er.exam_id
    left join examination_info as ei on er.exam_id = ei.exam_id
where er.score > ad.avg_score
    and timestampdiff(second, er.start_time, er.submit_time) < ad.avg_time
order by er.emp_id, er.exam_id