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

SELECT e.emp_id,emp_level,tag AS emp_tag
FROM exam_record  AS e JOIN examination_info  AS ex ON e.exam_id = ex.exam_id
                        JOIN emp_info AS em ON e.emp_id = em.emp_id
                        JOIN (
                        SELECT exam_id,AVG(score)  AS avg_score
                        FROM exam_record 
                        GROUP BY exam_id
                        ) AS score ON e.exam_id=score.exam_id
                        JOIN (
                        SELECT exam_id,AVG(TIMESTAMPDIFF(SECOND,start_time,submit_time))  AS avg_time
                        FROM exam_record 
                        GROUP BY exam_id
                        ) AS `time` ON e.exam_id = `time`.exam_id
WHERE  score>avg_score AND TIMESTAMPDIFF(second,start_time,submit_time) < avg_time AND emp_level <7
ORDER BY emp_id,ex.exam_id