题解 | 完成员工考核试卷突出的非领导员工
完成员工考核试卷突出的非领导员工
https://www.nowcoder.com/practice/422dcd6ae72c49c9bbec1aff90d69806
select ei.emp_id,emp_level,tag as exam_tag
from (
select avg(score) as avg_score ,avg(timestampdiff(minute ,start_time,submit_time)) as avg_time,exam_record.exam_id
from exam_record join examination_info on exam_record.exam_id=examination_info.exam_id
join emp_info on exam_record.emp_id=emp_info.emp_id
group by exam_record.exam_id
) as t1 join exam_record on t1.exam_id=exam_record.exam_id
join examination_info e on exam_record.exam_id = e.exam_id
join emp_info ei on exam_record.emp_id = ei.emp_id
where score>avg_score and timestampdiff(minute ,start_time,submit_time)<avg_time and emp_level<7
order by emp_id
