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

with
    avg_record as (
        select
            distinct exam_id,
            avg(timestampdiff(second,start_time,submit_time)) over (
                partition by
                    exam_id
            ) avg_time,
            avg(score) over (
                partition by
                    exam_id
            ) avg_score
        from
            exam_record
    )
select
    er.emp_id,
    emp_level,
    tag as exam_tag
from
    exam_record er
    join avg_record ar using(exam_id)
    join examination_info exi using(exam_id)
    join emp_info emi using(emp_id)
where
    timestampdiff(second,start_time,submit_time) < avg_time
    and score > avg_score
    and er.emp_id in (
        select
            emp_id
        from
            emp_info
        where
            emp_level < 7
    )
order by er.emp_id,er.exam_id 

1. datetime相减求秒：timestampdiff(second,start_time,end_time)

求天：timestampdiff(day,start_time,end_time)

1. join时如果两个表on条件的column名完全相同，可以用using(column)