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

 #计算平均分和平均时间
WITH avg_exam AS (
    SELECT
        ei.tag,
        AVG(TIMESTAMPDIFF(SECOND, er.start_time, er.submit_time)) AS avg_exam_time,
        AVG(er.score) AS avg_exam_score
    FROM
        exam_record AS er
        INNER JOIN
        examination_info AS ei ON er.exam_id=ei.exam_id
    GROUP BY
        ei.tag
    )
#查询满足条件的数据
SELECT
    er.emp_id,
    ep.emp_level,
    ei.tag AS exam_tag
FROM
    exam_record AS er
    INNER JOIN
    examination_info AS ei ON er.exam_id=ei.exam_id
    INNER JOIN
    emp_info AS ep ON er.emp_id=ep.emp_id
    INNER JOIN
    avg_exam AS ae ON ei.tag=ae.tag
WHERE
    er.score>ae.avg_exam_score
    AND
    TIMESTAMPDIFF(SECOND,er.start_time,er.submit_time)<ae.avg_exam_time
    AND
    ep.emp_level<7
ORDER BY
    er.emp_id,
    er.exam_id;