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

with
    t as (
        select
            tag,
            avg(score) as avs,
            avg(timestampdiff(second, start_time, submit_time)) as exam_time
        from
            emp_info as emi
            join exam_record er on emi.emp_id = er.emp_id
            join examination_info as exi on er.exam_id = exi.exam_id
        group by
            tag
    )
select
    emp_id,
    emp_level,
    tag as exam_tag
from
    emp_info 
    join exam_record using(emp_id) 
    join examination_info using(exam_id)
    join t using(tag)
where emp_level < 7
and score > t.avs
and timestampdiff(second, start_time, submit_time) < t.exam_time
order by emp_id,exam_id