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

select emp_id,emp_level,exam_tag from
(select exam_record.emp_id emp_id,emp_level,tag exam_tag,exam_record.exam_id,timestampdiff(second,start_time,submit_time) timebt,score,avg(timestampdiff(second,start_time,submit_time))over(partition by exam_record.exam_id) pjtime,avg(score)over(partition by exam_record.exam_id) pjscore
from 
exam_record inner join emp_info on exam_record.emp_id=emp_info.emp_id join examination_info on exam_record.exam_id=examination_info.exam_id) as newtable
where timebt<pjtime and score>pjscore and emp_level<7
order by emp_id,exam_id

用窗口函数算出平均的时间和得分