假设 100 是开始事件,101 是停止事件。这些应该交替出现,我想在它们不交替时找出答案。每个启动都应有一个先前的停止事件。给定此表:
ID EVENT_ID
10 100 // a start event
11 101 // a stop event
12 99 // some irrelevant event
13 100 // .. and so on:
14 101
15 100
16 99 // some irrelevant event
17 100 // <-- duplicate event: bad
18 100 // <-- duplicate event: bad again.
19 101
我省略了日期时间列。
如何找到 #15 和 #17,因为开始不应该连续出现两次?
结果应采用以下形式:
EVENT_ID FROM_ID FROM_DATETIME UPTO_ID UPTO_DATETIME
100 15 2014-01-01 14:00 17 2014-01-01 16:00
我想它可以像这样工作:
- 子选择,仅查找按日期时间排序的事件 100,101。
- 按EVENT_ID分组以使用count()
查找重复项..但我怀疑这会折叠结果,因为小组会忽略排序,
..而且我不知道如何获取两个(或更多!)各自的 DATETIME 值。
有可能在MySQL中找到这个吗?我可以在 PHP 中解决这个问题,但我宁愿避免这种情况。
出于方便起见,在我的示例中,所有行都具有相同的日期时间值。
DROP TABLE IF EXISTS T;
CREATE TABLE T
(`ID` int auto_increment primary key, `EVENT_ID` int, dt timestamp)
;
INSERT INTO T
(`ID`, `EVENT_ID`)
VALUES
(10, 100),
(11, 101),
(12, 99),
(13, 100),
(14, 101),
(15, 100),
(16, 99),
(17, 100),
(18, 100),
(19, 101)
;
select id, event_id, dt as up_to_dt, prevgood, prevdt as from_dt from
(
select
T.*
, if((@startstop = 100 and event_id = 101) or (@startstop = 101 and event_id = 100), 'good', 'bad') as goodbad
, @prevgood := if(if((@startstop = 100 and event_id = 101) or (@startstop = 101 and event_id = 100), 'good', 'bad') = 'bad', @prevgood, id) as prevgood
, @prevdt := if(if((@startstop = 100 and event_id = 101) or (@startstop = 101 and event_id = 100), 'good', 'bad') = 'bad', @prevdt, dt) as prevdt
, @startstop := event_id
from
T, (select @startstop:=101, @prevgood:=0, @prevdt:=0) vars
where event_id in (100, 101)
order by id
) sq
where goodbad = 'bad';
返回
+----+----------+---------------------+----------+---------------------+
| id | event_id | up_to_dt | prevgood | from_dt |
+----+----------+---------------------+----------+---------------------+
| 17 | 100 | 2014-01-20 09:12:20 | 15 | 2014-01-20 09:12:20 |
| 18 | 100 | 2014-01-20 09:12:20 | 15 | 2014-01-20 09:12:20 |
+----+----------+---------------------+----------+---------------------+