表结构及数据如下:
需求:
将记录按照时间顺序排列,每三条记录为一组,若第二条记录与第一条记录相差5分钟,则删除该记录,若第三条与第二条记录相差5分钟,则删除该记录,
第二组同理,遍历全表,按要求删除记录。
例如:
9:55:00,10:00:00,10:05:00三条记录为一组,后两条记录均满足条件,均删除。(注意:虽然10:10:00的记录与10:05:00的记录也相差5分钟,但不是同一组,不能删除)
处理SQL如下:
WITH T AS
(SELECT TIME_,
DEL_TIME_2,
CASE
WHEN LABEL_ = 2 THEN
NULL
ELSE
DEL_TIME_3
END AS DEL_TIME_3
FROM (SELECT TIME_,
LABEL_,
CASE
WHEN (TO_DATE(TIME_2, 'HH24:MI:SS') -
TO_DATE(TIME_, 'HH24:MI:SS')) * 24 * 60 = 5 THEN
TIME_2
ELSE
NULL
END AS DEL_TIME_2,
CASE
WHEN (TO_DATE(TIME_3, 'HH24:MI:SS') -
TO_DATE(TIME_2, 'HH24:MI:SS')) * 24 * 60 = 5 THEN
TIME_3
ELSE
NULL
END AS DEL_TIME_3
FROM (SELECT TIME_, TIME_2, TIME_3, MOD(RN, 3) AS LABEL_
FROM (SELECT TIME_,
&n