题解 | #SQL28#
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
要换算成秒数来计算
SELECT t1.exam_id,
ei.duration,
ei.release_time
FROM (
SELECT exam_id,
TIMESTAMPDIFF(SECOND, start_time, submit_time) AS timespent,
ROW_NUMBER() OVER (PARTITION BY exam_id ORDER BY (SELECT timespent)) AS r_q,
ROW_NUMBER() OVER (PARTITION BY exam_id ORDER BY (SELECT timespent) DESC) AS r_s
FROM exam_record
WHERE submit_time IS NOT NULL
) AS t1
JOIN examination_info ei USING (exam_id)
GROUP BY t1.exam_id
HAVING (SUM(CASE WHEN r_s = 2 THEN timespent ELSE 0 END) - SUM(CASE WHEN r_q = 2 THEN timespent ELSE 0 END)) > 0.5 * ei.duration*60
ORDER BY t1.exam_id DESC
