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

select i.emp_id,emp_level,tag exam_tag
from emp_info i
join 
(select distinct emp_id,r.exam_id,tag
from exam_record r
join 
(select r.exam_id,tag,avg(timestampdiff(second,start_time,submit_time)) avg_time,
avg(score) avg_sc
from exam_record r
join examination_info e using(exam_id)
group by r.exam_id,tag) m
using(exam_id)
where score>avg_sc and timestampdiff(second,start_time,submit_time) <avg_time) f
using(emp_id)
where emp_level<7
order by i.emp_id,exam_id;
即使子查询中已经重命名一个表格了，但外层还是不能直接用重命名。
这题还要注意一些列名的变化和两表的联结词需要指定表格。