题解 | 完成员工考核试卷突出的非领导员工
完成员工考核试卷突出的非领导员工
https://www.nowcoder.com/practice/422dcd6ae72c49c9bbec1aff90d69806
with t as ( select tag, avg(score) as avs, avg(timestampdiff(second, start_time, submit_time)) as exam_time from emp_info as emi join exam_record er on emi.emp_id = er.emp_id join examination_info as exi on er.exam_id = exi.exam_id group by tag ) select emp_id, emp_level, tag as exam_tag from emp_info join exam_record using(emp_id) join examination_info using(exam_id) join t using(tag) where emp_level < 7 and score > t.avs and timestampdiff(second, start_time, submit_time) < t.exam_time order by emp_id,exam_id