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

SELECT
    emp_id,
    emp_level,
    exam_tag
FROM(
    SELECT
        er.emp_id,
        er.exam_id,
        emp_level,
        ROUND(TIMESTAMPDIFF(SECOND, start_time, submit_time) / 60, 1) AS time_consumed,
        score,
        avg_time, # 平均用时
        avg_score, # 平均分数
        tag AS exam_tag
    FROM exam_record er 
    JOIN examination_info ei
    ON er.exam_id = ei.exam_id
    JOIN emp_info emp
    ON er.emp_id = emp.emp_id  # 2. 最重要的一步，把平均用时和分数和所需变量JOIN到一起
    JOIN(
        SELECT
            exam_id,
            ROUND(AVG(TIMESTAMPDIFF(SECOND, start_time, submit_time) / 60), 1) AS avg_time,
            AVG(score) AS avg_score
        FROM exam_record
        GROUP BY exam_id
    ) AS tb1 # 1. 找到每个exam_id平均用时和平均分数
    ON tb1.exam_id = er.exam_id
    WHERE emp_level < 7 # 条件1 非领导员工 emp_level < 7
) AS tb2
WHERE time_consumed < avg_time AND score > avg_score # 条件2和3
ORDER BY emp_id ASC, exam_id ASC # 条件4