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

select r.emp_id, emp_level , tag as exam_tag
from 
(
    select exam_id, emp_id, diff,score , avg(diff)over(partition by exam_id) as exam_diff
    , avg(score)over(partition by exam_id) as exam_score
    from 
    (
    select  timestampdiff(second, start_time, submit_time) as diff , score, emp_id, r.exam_id 
    from exam_record  r 
    )r 
)r
inner join  examination_info  e on r.exam_id=e.exam_id
inner join emp_info  em on r.emp_id = em.emp_id
where diff<exam_diff and score>exam_score
and emp_level <7
order by r.emp_id, r.exam_id

开始还以为分类应该包括多个exam_id