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

#求解每个类目平均作答时长和分数
with t1 as(
select tag, 
avg(score) as avg_score,
avg(timestampdiff(second,start_time,submit_time)) as avg_time
from examination_info b left join exam_record c using(exam_id)
group by tag)
#表连接
select a.emp_id, emp_level, b.tag as exam_tag
from emp_info a left join exam_record c on a.emp_id = c.emp_id
left join examination_info b on c.exam_id = b.exam_id
left join t1 on b.tag = t1.tag
where score > avg_score 
and timestampdiff(second,start_time,submit_time) < avg_time
and emp_level < 7
order by a.emp_id,c.exam_id