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

select
    emp_id,
    emp_level,
    tag as exam_tag
from
    (
        select
            exam_record.emp_id,
            emp_info.emp_level,
            exam_record.exam_id,
            examination_info.tag,
            case
                when timestampdiff(second, exam_record.start_time, submit_time) < avg_record.avg_time
                and exam_record.score > avg_record.avg_score then 1
                else 0
            end as is_good
        from
            exam_record
            join (
                select
                    exam_id,
                    avg(timestampdiff(second, start_time, submit_time)) as avg_time,
                    avg(score) as avg_score
                from
                    exam_record
                group by
                    exam_id
            ) avg_record on exam_record.exam_id = avg_record.exam_id
            join examination_info on exam_record.exam_id = examination_info.exam_id
            join emp_info on exam_record.emp_id = emp_info.emp_id
        where
            emp_info.emp_level < 7
        order by
            exam_record.emp_id,
            examination_info.exam_id
    ) total
where 
    is_good = 1

思路最清晰的一集！！