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

题目描述

请你找到作答每类试卷的突出非领导员工，并输出他们的员工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