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

select
    emp_record.emp_id,
    emp_record.emp_level,
    tag
from 
    (
        select 
            emp_level,
            emi.emp_id,
            exam_id,
            timestampdiff(second, start_time, submit_time) as cost_time,
            score
        from emp_info as emi
        inner join exam_record er on emi.emp_id=er.emp_id and emp_level < 7
    ) as emp_record
    inner join 
    (
        select 
            exam_id,
            avg(timestampdiff(second, start_time, submit_time)) as avg_cost_time,
            avg(score) as avg_score
        from  exam_record
        group by
            exam_id
    ) as avg_tb on emp_record.exam_id=avg_tb.exam_id and cost_time < avg_cost_time and score > avg_score
    inner join examination_info on emp_record.exam_id=examination_info.exam_id 
order by
    emp_record.emp_id, emp_record.exam_id