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

WITH avg_info AS (SELECT
    exam_id,
    AVG((MINUTE(submit_time)+SECOND(submit_time)/60) - (MINUTE(start_time)+SECOND(start_time)/60))  AS avg_time,
    AVG(score)  AS avg_score
FROM exam_record 
GROUP BY exam_id)

SELECT 
    exam_record.emp_id,
    emp_level,
    tag AS exam_tag
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
JOIN avg_info ON avg_info.exam_id = exam_record.exam_id
WHERE 
emp_level < 7
AND (MINUTE(submit_time)+SECOND(submit_time)/60) - (MINUTE(start_time)+SECOND(start_time)/60) < avg_info.avg_time
AND score > avg_info.avg_score
ORDER BY exam_record.emp_id