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

# 请你找到作答每类试卷的突出非领导员工，并输出他们的
# 员工ID（emp_info），员工等级（emp_info）和突出试卷类别（examination_info）
# 并按照员工ID升序排序，若某员工两类试卷都突出，则按照试卷ID升序排序。

# 9001试卷员工id，分数，平均分，时间，平均时间
# 9002试卷员工id，分数，平均分，时间，平均时间

# 查询每张卷子的平均分和平均时间备用
with avg_every as(
    select t1.exam_id exam_id
    ,tag exam_tag
    ,avg(score) avg_sc
    ,avg(TIMESTAMPDIFF(second,start_time,submit_time)) avg_time
    from exam_record t1
    join examination_info t2
    on t1.exam_id=t2.exam_id
    group by t1.exam_id,tag
)
,emp_target as(
    select emp_id
    ,t1.exam_id exam_id
    ,exam_tag
    from exam_record t1
    join avg_every t2
    on t1.exam_id=t2.exam_id
    where t1.score>t2.avg_sc and TIMESTAMPDIFF(second,start_time,submit_time)<avg_time
)
select t1.emp_id emp_id,emp_level,exam_tag
from emp_target t1
join emp_info t2
on t1.emp_id=t2.emp_id
where emp_level<7
order by emp_id;
# 本题要查好多字段，但分组字段只有一个。
# 先用临时表只查出分组字段及需要分组计算的字段再与其他不需要分组的字段去join在一起做好过滤。

反复复习本题思路