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

WITH avg_score AS(
    SELECT 
        exam_id,
        avg(score) AS mean_score
    FROM exam_record
    GROUP BY exam_id
),
avg_cost_time AS(
    SELECT 
        exam_id,
        avg(TIMESTAMPDIFF(MINUTE,start_time,submit_time)) AS mean_time_diff
    FROM exam_record
    GROUP BY exam_id
),
avg_total AS (SELECT
    avg_score.exam_id,
    avg_score.mean_score,
    avg_cost_time.mean_time_diff
FROM avg_score
LEFT JOIN avg_cost_time
ON avg_score.exam_id=avg_cost_time.exam_id),
emp_select AS (
    SELECT 
        a.emp_id,
        a.exam_id
    FROM exam_record AS a
    LEFT JOIN avg_total AS b
    ON a.exam_id=b.exam_id
    WHERE 
        a.score>b.mean_score AND 
        TIMESTAMPDIFF(MINUTE,a.start_time,a.submit_time)<b.mean_time_diff
)
SELECT 
    a.emp_id,
    c.emp_level,
    b.tag AS exam_tag
FROM emp_select AS a
LEFT JOIN examination_info AS b 
ON a.exam_id=b.exam_id
LEFT JOIN emp_info AS c
ON a.emp_id=c.emp_id
WHERE c.emp_level<7
;

• 其实缺乏排序，但是可以运行……