删除数据问题

[alice鑫鑫]

id time
123aaa 2010-01-01 11:11:11
123bbb 2010-01-01 11:11:21
111aaa 2010-01-01 11:11:11
111bbb 2010-01-01 11:11:21
111ccc 2010-01-01 11:11:55
111ddd 2010-01-01 11:11:10
111eee 2010-01-01 11:11:12

要求删除数据，条件是 id前3位一样，并且时间相差10s的数据全删除

[小灰狼W]

以以上数据为例，结果是什么样的

[iqlife]

先帮女人顶下!!!!

[iqlife]

并且时间相差10s的数据全删除
这个是指前后紧挨的记录么的么?

[iqlife]

例如:

111aaa 2010-01-01 11:11:11
111bbb 2010-01-01 11:11:21
111ccc 2010-01-01 11:11:35
111ddd 2010-01-01 11:11:33
111eee 2010-01-01 11:11:45
111eee 2010-01-01 11:11:23

怎么删除

[tangren]

DELETE FROM test t
 WHERE EXISTS (SELECT 1
          FROM (SELECT *
                  FROM (SELECT id,
                               TIME,
                               lag(id) over(ORDER BY TIME) p_id,
                               lag(TIME) over(ORDER BY TIME) p_time
                          FROM test)
                 WHERE substr(id, 1, 3) = substr(p_id, 1, 3) AND
                       (TIME - p_time) / 86400 <= 10) a
         WHERE a.id = t.id OR
               a.p_id = t.id);

[alice鑫鑫]

这个问题有点不对，我想想，然后再讨论，谢谢大家，请稍定~~~~~~~~

[tangren]

--上面有点问题，这个
DELETE FROM test t
 WHERE EXISTS (SELECT 1
          FROM (SELECT a.id aid, b.id bid
                  FROM test a, test b
                 WHERE substr(a.id, 1, 3) = substr(b.id, 1, 3) AND
                       (b.time - a.time) BETWEEN 0 AND 1 / 8640)
         WHERE aid NOT IN (SELECT aid
                             FROM (SELECT a.id aid, b.id bid
                                     FROM test a, test b
                                    WHERE substr(a.id, 1, 3) = substr(b.id, 1, 3) AND
                                          (b.time - a.time) BETWEEN 0 AND 1 / 8640)
                            GROUP BY aid
                           HAVING COUNT(aid) = 1) AND
               (t.id = aid OR t.id = bid));