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

SELECT E1.emp_id,E1.emp_level,E3.tag AS exam_tag
FROM (
    SELECT emp_id,emp_level
    FROM emp_info
    WHERE emp_level < 7
) E1
LEFT JOIN 
(
    SELECT emp_id,exam_id
        ,time_to_sec(timediff(submit_time,start_time)) AS exam_time
        ,score
        ,avg(time_to_sec(timediff(submit_time,start_time))) OVER(PARTITION BY exam_id) as avg_time
        ,avg(score) OVER(PARTITION BY exam_id) as avg_score
    FROM exam_record
) E2
ON E1.emp_id = E2.emp_id
LEFT JOIN examination_info E3
ON E2.exam_id = E3.exam_id
WHERE exam_time < avg_time
AND score > avg_score
;