题解 | #SQL28#

要换算成秒数来计算

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