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

题目描述

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

拆解一下题目，我们要做的事情如下：

1、找到作答每类试卷的突出非领导员工，题目中给出的定义为：

作答用时少于同类试卷平均值 且 个人平均分数大于同类试卷总体平均值的员工

我们先看 作答用时少于同类试卷平均值 ：

作答用时可以从 exam_record中获取，但exam_record表中没有直接给出作答时间，

我们需要根据 start_time开始作答时间字段和submit_time交卷时间字段计算出来，使用timestampdiff函数，即：

timestampdiff(second, re.start_time,re.submit_time)

平均作答用时根据试卷类型分组，使用avg聚合即可。

再看个人分数大于同类试卷总体平均值：

同类试卷总体平均分 直接从 exam_record表中对score字段聚合即可。

我们使用with as创建临时表存储每类试卷的平均分和平均用时，方便后续使用。

with avg_every as(
    select
        avg(timestampdiff(second, re.start_time,re.submit_time)) as time_avg,
        avg(re.score) as score_avg,
        re.exam_id
    from
        emp_info inf
    join
        exam_record re on re.emp_id = inf.emp_id
    group by
        re.exam_id
)

接下来，我们筛选出符合条件的成绩：

exam_target as(
    select
        r.emp_id,
        r.exam_id
    from
        avg_every a
        join exam_record r on r.exam_id = a.exam_id
    where
        r.score > a.score_avg and timestampdiff(second, r.start_time,r.submit_time) < a.time_avg
)

题目中还有一个筛选条件是"非领导员工"，在emp_info表内，可以在输出结果的时候一起筛选。

where
    i.emp_level < 7

2、输出结果和排序

输出他们的员工ID，员工等级和突出试卷类别并按照员工ID升序排序，
若某员工两类试卷都突出，则按照试卷ID升序排序。

我们需要输出exam_tag,以及判断员工等级，连接这两个表即可。

select
    t.emp_id,
    i.emp_level,
    e.tag
from
    exam_target t
join
    examination_info e on t.exam_id = e.exam_id
join
    emp_info i on i.emp_id = t.emp_id
where
    i.emp_level < 7
order by
    i.emp_id asc,e.exam_id asc

3、完整代码：

with avg_every as(
    select
        avg(timestampdiff(second, re.start_time,re.submit_time)) as time_avg,
        avg(re.score) as score_avg,
        re.exam_id
    from
        emp_info inf
    join
        exam_record re on re.emp_id = inf.emp_id
    group by
        re.exam_id
)
,exam_target as(
    select
        r.emp_id,
        r.exam_id
    from
        avg_every a
        join exam_record r on r.exam_id = a.exam_id
    where
        r.score > a.score_avg and timestampdiff(second, r.start_time,r.submit_time) < a.time_avg
)
select
    t.emp_id,
    i.emp_level,
    e.tag
from
    exam_target t
join
    examination_info e on t.exam_id = e.exam_id
join
    emp_info i on i.emp_id = t.emp_id
where
    i.emp_level < 7
order by
    i.emp_id asc,e.exam_id asc