/*注意什么时候用聚合函数什么时候用where什么时候用having；
目标列名称是否需要修改----tag改成exam_tag
联表：from emp_info t1 join exam_record t3 on t1.emp_id = t3.emp_id
join examination_info t2 on t3.exam_id = t2.exam_id
计数：试卷作答用时平均值：avg(timestampdiff(minute,start_time,  submit_time)) as avg_time
     试卷的平均分：avg(score) as avg_score
分组与排序：
group by exam_id,如果用聚合函数不写group by,二者作用冲突
order by emp_id,exam_id
筛选：where emp_level < 7
*/
select t.emp_id
,t.emp_level
,t.exam_tag
from
(select t3.exam_id
,t1.emp_id
,t2.tag as exam_tag
,t1.emp_level
,t3.score
,timestampdiff(minute,start_time,submit_time) as time
,avg(timestampdiff(minute,start_time,submit_time)) over(partition by t3.exam_id) as avg_time 
,avg(score) over(partition by t3.exam_id)as avg_score
from emp_info t1 
join exam_record t3 on t1.emp_id = t3.emp_id
join examination_info t2 on t3.exam_id = t2.exam_id
where emp_level < 7
) t
where t.time < t.avg_time
and t.score > t.avg_score
order by emp_id,exam_id