题解 | 完成员工考核试卷突出的非领导员工
完成员工考核试卷突出的非领导员工
https://www.nowcoder.com/practice/422dcd6ae72c49c9bbec1aff90d69806
select emp_id, emp_level, exam_tag from (
select emp_id, emp_level, exam_tag, start_time, submit_time, score
from(
select emp_id, emp_level, tag as exam_tag, start_time, submit_time, score,
avg(timestampdiff(second, start_time, submit_time)) over(partition by tag) as avg_time,
avg(score) over(partition by tag) as avg_score
from emp_info em
left join exam_record ex
using(emp_id)
left join examination_info ei
using(exam_id)
where emp_level<7
) t
where timestampdiff(second, start_time, submit_time) < avg_time
and score > avg_score
) m
order by emp_id
查看5道真题和解析