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

with avg_exam  AS (
    SELECT exam_id,AVG(score) avg_score,AVG(timestampdiff(second,start_time,submit_time)) avg_time
    FROM exam_record
    GROUP BY exam_id)

SELECT table1.emp_id,table1.emp_level,table2.tag exam_tag
FROM(
    SELECT a.emp_id,b.emp_level,a.exam_id
    FROM(
        SELECT t1.emp_id,t1.exam_id
        FROM exam_record t1
        INNER JOIN avg_exam t2
        ON t1.exam_id = t2.exam_id
        WHERE t1.score > t2.avg_score
        AND timestampdiff(second,t1.start_time,t1.submit_time) < t2.avg_time
    ) a
    INNER JOIN emp_info b
    ON a.emp_id = b.emp_id
    WHERE a.emp_id IN(
        SELECT DISTINCT emp_id
        FROM emp_info
        WHERE emp_level < 7
    )
) table1
INNER JOIN examination_info table2
ON table1.exam_id = table2.exam_id