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

WITH t1 AS (
    SELECT 
    exam_id,
    avg(score) average_score,
    avg(timestampdiff(second,start_time,submit_time)/60) average_time
    FROM exam_record
    GROUP BY exam_id
)
SELECT 
m.emp_id,
emp_level,
tag
FROM exam_record m 
JOIN t1 ON m.exam_id = t1.exam_id
JOIN emp_info p on m.emp_id = p.emp_id
JOIN examination_info i on m.exam_id = i.exam_id
WHERE 
    timestampdiff(second,start_time,submit_time)/60 < t1.average_time
    AND 
    score > t1.average_score
    AND
    emp_level < 7
ORDER BY m.emp_id, m.exam_id