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

select
	emp_id,
    emp_level,
    tag as exam_tag
from(
select
        emp_id,
        emp_level,
        tag,
        exam_id,
        timestampdiff(second,start_time,submit_time) as time1,
        score
    from exam_record
    join examination_info using (exam_id)
    join emp_info using(emp_id)
    ) as ub1
join (
	select
		tag,
		avg(timestampdiff(second,start_time,submit_time)) as avg_time,
		avg(score) as avg_score
	from exam_record
    join examination_info using (exam_id)
    group by tag
)ub2 using (tag)
where 
	ub1.score  > ub2.avg_score 
    and ub1.time1 < ub2.avg_time 
    and emp_level < 7 
order by emp_id,exam_id