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

SELECT
  t.emp_id,
  i.emp_level,
  e.tag
FROM
  exam_record t
JOIN
  (SELECT
     exam_id,
     AVG(TIMESTAMPDIFF(SECOND, start_time, submit_time)) AS time_avg,--求结束-开始的平均值
     AVG(score) AS score_avg 分数的平均值
   FROM exam_record
   GROUP BY exam_id) a ON t.exam_id = a.exam_id
JOIN emp_info i ON t.emp_id = i.emp_id AND i.emp_level < 7
JOIN examination_info e ON t.exam_id = e.exam_id
WHERE
  t.score > a.score_avg--分数>平均分数
  AND TIMESTAMPDIFF(SECOND, t.start_time, t.submit_time) < a.time_avg 差额平均值小于所有差额的平均值
ORDER BY t.emp_id ASC, e.exam_id ASC;