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

with a as(
    select 
    avg(timestampdiff(second,start_time,submit_time)) tdiff,
    avg(score) avgs,
    exam_id
    from exam_record
    group by exam_id),
    b as(
        select 
        emp_id,emp_level,tag exam_tag
        from a
        join exam_record using(exam_id)
        join emp_info using(emp_id)
        join examination_info using(exam_id)
        where timestampdiff(second,start_time,submit_time)<tdiff
        and score>avgs and emp_level <7
        order by emp_id,exam_id)
select *
from b