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

/*该类型的突出员工:作答用时少于同类试卷平均值,个人平均分数大于同类试卷总体平均值
每类试卷,员工等级和突出试卷类别并按照员工ID升序排序,两类试卷都突出，则按照试卷ID升序排序
-- 每类试卷id平均作答时长
--每类试卷平均分数
--每个员工是否突出*/
with t as(
    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)
    ,
t1 as (
    select e.emp_id,e.exam_id,
    if (timestampdiff(second,e.start_time,e.submit_time)<t.avg_time,1,0) as is_time,    
    if (e.score>t.avg_score,1,0 ) as is_socre
    from exam_record e 
    left join t on e.exam_id=t.exam_id)


select e.emp_id,e.emp_level,e1.tag as exam_tag
from t1 
left join emp_info e on  e.emp_id=t1.emp_id
left join  examination_info e1 on e1.exam_id=t1.exam_id
where is_time=1 and is_socre=1 
    and e.emp_level<7
order by e.emp_id,e1.exam_id