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

SELECT
    emp_id,
    emp_level,
    tag AS exam_tag
FROM
    exam_record
    JOIN emp_info USING (emp_id)
    JOIN examination_info USING (exam_id)
    JOIN (
        SELECT
            exam_id,
            AVG(TIMESTAMPDIFF(MINUTE, start_time, submit_time)) AS avg_time,
            AVG(score) AS avg_score
        FROM
            exam_record
        GROUP BY
            exam_id
    ) AS sub USING (exam_id)
WHERE
    TIMESTAMPDIFF(MINUTE, start_time, submit_time) < avg_time
    AND score > avg_score
    AND emp_level<7