题解 | 完成员工考核试卷突出的非领导员工 还是比较简单的

with t1 as 
(
select b.emp_level ,a.*,timestampdiff(second,start_time,submit_time) as alltime,c.tag as exam_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
)

,t2 as (
select *,avg(score)over(partition by exam_id) as avg_score,avg(alltime)over(partition by exam_id) as avg_time
from t1 
)

select emp_id,emp_level,exam_tag
from t2 
where alltime < avg_time and score > avg_score and emp_level < 7
order by emp_id , exam_id