select distinct exam_id, duration, release_time from (select exam_id as exam_id, duration, release_time, sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub from ( select e_i.exam_id, duration, release_time, timestampdiff(minute, start_time, submit_time) as costtime, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2 from exam_record e_r join examination_info e_i on e_r.exam_id = e_i.exam_id where submit_time is not null ) table1 group by exam_id ) table2 where sub * 2 >= duration order by exam_id desc;把这段sql改写成oracle语法

SELECT DISTINCT exam_id, duration, release_time
FROM (
SELECT exam_id, duration, release_time, SUM(CASE WHEN rank1 = 2 THEN costtime WHEN rank2 = 2 THEN -costtime ELSE 0 END) AS sub
FROM (
SELECT e_i.exam_id, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS costtime,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC) rank1,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) ASC) rank2
FROM exam_record e_r JOIN examination_info e_i
ON e_r.exam_id = e_i.exam_id
WHERE submit_time IS NOT NULL
) table1
GROUP BY exam_id, duration, release_time
) table2
WHERE sub * 2 >= duration
ORDER BY exam_id DESC;