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

SELECT 
    er.emp_id,
    emp.emp_level,
    t1.tag exam_tag
FROM
    (SELECT
        ei.exam_id,
        ei.tag ,
        AVG(er.score) avg_score,
        AVG(TIMESTAMPDIFF(MINUTE, er.start_time, er.submit_time)) avg_time
    FROM exam_record  er
    JOIN examination_info ei ON ei.exam_id = er.exam_id
    GROUP BY ei.exam_id, ei.tag) t1
JOIN exam_record er ON er.exam_id = t1.exam_id
JOIN emp_info emp ON emp.emp_id = er.emp_id
WHERE er.score > avg_score AND TIMESTAMPDIFF(MINUTE, er.start_time, er.submit_time) <  avg_time AND emp_level < 7
ORDER BY er.emp_id, t1.exam_id