001 2014/8/1 16:00:13 aa 001 2014/8/1 16:01:55 aa 001 2014/8/1 16:02:28 bb 002 2014/8/1 16:06:28 bb j就是只有第一列和第三列都相同的情况下,如果时间相差5分钟之内,则时间相差5分钟的那几条记录全部删除,否则保留。
如果两条记录间隔不足5分钟,是两条记录都不要还是保留一条 下面语句是保留最后一条记录 列名为标明,设定从左到右分别为A、B、C DELETE FROM T T1 WHERE EXISTS(SELECT 1 FROM T WHERE B-5/60/24<T1.B AND A=T1.A AND C=T1.C)
全部删除的话这样写 DELETE FROM T T1 WHERE EXISTS(SELECT 1 FROM T WHERE (B-5/60/24<T1.B OR B+5/60/24>T1.B) AND A=T1.A AND C=T1.C)
确实是少写了一个条件,判断exists的时候需要把自身剔除 DELETE FROM T T1 WHERE EXISTS(SELECT 1 FROM T WHERE (B-5/60/24<T1.B OR B+5/60/24>T1.B) AND A=T1.A AND C=T1.C AND ROWID<>T1.ROWID)
未测试,试一下吧 DELETE FROM T WHERE (A,C) IN ( select A,C FROM T GROUP BY A,C HAVING MIN(B-LAG(B,1,B-1)OVER(PARTITION BY A,C ORDER BY B))<5/60/24)
001 2014/8/1 16:01:55 aa
001 2014/8/1 16:02:28 bb
002 2014/8/1 16:06:28 bb
j就是只有第一列和第三列都相同的情况下,如果时间相差5分钟之内,则时间相差5分钟的那几条记录全部删除,否则保留。
下面语句是保留最后一条记录
列名为标明,设定从左到右分别为A、B、C
DELETE FROM T T1
WHERE EXISTS(SELECT 1 FROM T WHERE B-5/60/24<T1.B AND A=T1.A AND C=T1.C)
DELETE FROM T T1
WHERE EXISTS(SELECT 1 FROM T
WHERE (B-5/60/24<T1.B OR B+5/60/24>T1.B)
AND A=T1.A AND C=T1.C)
DELETE FROM T T1
WHERE EXISTS(SELECT 1 FROM T
WHERE (B-5/60/24<T1.B OR B+5/60/24>T1.B)
AND A=T1.A AND C=T1.C AND ROWID<>T1.ROWID)
DELETE FROM T
WHERE (A,C) IN (
select A,C FROM T
GROUP BY A,C
HAVING MIN(B-LAG(B,1,B-1)OVER(PARTITION BY A,C ORDER BY B))<5/60/24)