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

select
	t4.emp_id,
	t4.emp_level,
	t4.tag as exam_tag
from
	(select
		t1.emp_id,
		t2.emp_level,
		t3.tag,
		t1.exam_id,
		t1.submit_time-t1.start_time as time,
		t1.score
	from 
		exam_record t1 left join emp_info t2
	on 
		t1.emp_id=t2.emp_id
	left join 
		examination_info t3
	on 
		t1.exam_id=t3.exam_id
	where t2.emp_level<7) t4
left join
	(select 
    	t3.tag,
    	avg(t1.submit_time-t1.start_time) as avg_time,
    	avg(t1.score) as avg_score
	from 
		exam_record t1 
	left join 
		examination_info t3 
	on 
		t1.exam_id=t3.exam_id
	group by t3.tag) t5
on 
	t4.tag=t5.tag
where 
	t4.time<t5.avg_time 
	and 
	t4.score>t5.avg_score
order by 
	t4.emp_id asc,t4.exam_id asc