mysql - MySQL 根据相邻行的值删除行

Question

我有一个如下所示的表格:

eventId    activity    timestamp
1          0           2012-10-22 20:10:00
2          0           2012-10-22 20:10:20
3          1           2012-10-22 20:11:25
4          1           2012-10-22 20:12:20
5          1           2012-10-22 20:12:22
6          0           2012-10-22 20:12:30  <--
7          1           2012-10-22 20:12:25  <--
8          0           2012-10-22 20:14:46
9          0           2012-10-22 20:14:48
10         1           2012-10-22 20:15:45
11         0           2012-10-22 20:16:00
12         0           2012-10-22 20:17:00
13         0           2012-10-22 20:17:13

我想删除事件为 0 并且按时间顺序不位于事件为 1 的行旁边的每一行。因此，在此示例中，我将删除 eventID 为 1、8、12 和 13 的行。这是可能的用于异步插入表中的事件，如第 6 行和第 7 行所示。

我知道我可以通过检查每个单独的行并发出查询来删除它(如果它符合我的条件)来循环执行此操作，但这是非常低效的。我想知道是否可以在一个查询中完成这一切。

看起来我是否可以做类似的事情

delete from mytable
where activity = 0
and (rownumber()+1).activity = 0
and (rownumber()-1).activity = 0
order by timestamp

这很简单，但我认为这样的功能是不可能的。

Answer 1

以下是您选择要保留的所有记录的方法:

select m.eventId
from (
  select t1.eventId, 
      max(t2.timestamp) as previousTime,
      min(t3.timestamp) as nextTime
  from mytable t1
  left outer join mytable t2 on t1.eventId <> t2.eventId and t2.timestamp < t1.timestamp
  left outer join mytable t3 on t1.eventId <> t3.eventId and t3.timestamp > t1.timestamp
  group by t1.eventId
) m
left outer join mytable tb on m.previousTime = tb.timestamp
left outer join mytable ta on m.nextTime = ta.timestamp
where tb.activity = 1
    or ta.activity = 1

SQL Fiddle Example

然后您可以执行以下操作:

delete from mytable
where mytable.eventId not in ( ... ) <--above query