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

第一种方法：

-- 第一步：找出非领导员工
with emp_info_workers as (
select emp_id,emp_level
from emp_info
where emp_level < 7),

-- 第二步：计算答题时间和分数的平均值
exam_record_avg as (
select emp_id,exam_id,diff_time,score,avg(diff_time) over(partition by exam_id) as avg_time,avg(score) over(partition by exam_id) as avg_score
    from (
select emp_id,exam_id,timestampdiff(second,start_time,submit_time) as diff_time,score
    from exam_record) t1
)

-- 第三步:找出优秀员工

/*select ea.emp_id,exam_id,emp_level
from exam_record_avg ea inner join emp_info_workers ew on ea.emp_id = ew.emp_id
where diff_time < avg_time and score > avg_score */

-- 第四步：与考卷信息表关联，输出最终结果
select ea.emp_id,emp_level,ei.tag
from exam_record_avg ea inner join emp_info_workers ew on ea.emp_id = ew.emp_id
inner join examination_info ei on ea.exam_id = ei.exam_id
where diff_time < avg_time and score > avg_score 
order by ea.emp_id,ea.exam_id