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

with avge as(
    select 
    exam_id,avg(timestampdiff(second,start_time,submit_time)/60) as avg_time,avg(score) as avg_score
    from exam_record
    group by exam_id
),

filter as(
select
e1.emp_id as emp_id,e2.exam_id as exam_id,emp_level
from emp_info e1
inner join exam_record e2 on e1.emp_id=e2.emp_id
inner join avge a on e2.exam_id=a.exam_id
where emp_level<7 and timestampdiff(second,start_time,submit_time)/60<avg_time and score>avg_score
)

select
f.emp_id as emp_id,emp_level,tag as exam_tag
from filter f
inner join examination_info e on f.exam_id=e.exam_id;