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

WITH
    t AS (
        SELECT
            a.emp_id,
            b.emp_level,
            TIMESTAMPDIFF(SECOND, a.start_time, a.submit_time) AS duration_time,
            a.score,
            c.tag AS exam_tag
        FROM
            exam_record AS a
            JOIN emp_info AS b ON a.emp_id = b.emp_id
            JOIN examination_info AS c ON a.exam_id = c.exam_id
        WHERE
            b.emp_level < 7 
    )
SELECT
    emp_id,
    emp_level,
    exam_tag
FROM
    t
WHERE
    (
        (
            exam_tag = '企业文化'
            AND score > (
                SELECT
                    AVG(score)
                FROM
                    t
                WHERE
                    exam_tag = '企业文化'
            )
            AND duration_time < (
                SELECT
                    AVG(duration_time)
                FROM
                    t
                WHERE
                    exam_tag = '企业文化'
            )
        )
        OR (
            exam_tag = '技术水平'
            AND score > (
                SELECT
                    AVG(score)
                FROM
                    t
                WHERE
                    exam_tag = '技术水平'
            )
            AND duration_time < (
                SELECT
                    AVG(duration_time)
                FROM
                    t
                WHERE
                    exam_tag = '技术水平'
            )
        )
    )

先对考试信息表进行预处理，与另外两个表进行连接并得到每个人的考试分数，科目，作答时间并筛去emp_level>=7的领导,然后使用临时表t进行操作选出每个科目的优秀员工