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

#员工信息表 emp_id
#考核试卷信息表 exam_id
#试卷作答记录表 emp_id+exan_id+start_time

#emp_id+emp_level+exam_tag+score+score_avg+timestampdiff(start.submit)

#员工等级小于7 2021


select
emp_id
,emp_level
,tag
from 
    (select
    emp_id
    ,exam_id
    ,time
    ,avg(time)over(partition by exam_id) as avg_time
    ,score
    ,avg(score)over(partition by exam_id) as avg_score
    ,emp_level
    ,tag
    from
        (select
        a.emp_id
        ,a.exam_id
        ,abs(timestampdiff(minute,a.submit_time,a.start_time)) as time
        ,a.score
        ,b.emp_level
        ,c.tag
        from exam_record  a 
        left join emp_info b
        on a.emp_id = b.emp_id
        left join 
        examination_info c
        on a.exam_id = c.exam_id
        where left(start_time,4)=2021) t1)t2
where time<avg_time and score>avg_score and emp_level<7
order by emp_id;