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

SELECT e.emp_id AS emp_id, emp_level, tag AS exam_tag
FROM exam_record e
LEFT JOIN (
    SELECT 
        exam_id, 
        AVG(TIMESTAMPDIFF(SECOND, start_time, submit_time)) AS avg_time, 
        AVG(score) AS avg_score
    FROM exam_record
    GROUP BY exam_id
) a ON e.exam_id = a.exam_id
LEFT JOIN emp_info i ON e.emp_id = i.emp_id
LEFT JOIN examination_info f ON e.exam_id = f.exam_id
WHERE TIMESTAMPDIFF(SECOND, start_time, submit_time) < avg_time
    AND score > avg_score
    AND emp_level < 7
ORDER BY e.emp_id, emp_level, tag