MySQL查找行

Question

MySQL查找行

5

假设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

我忘记了DATETIME列。

如何找到#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

我认为可以这样实现：
- 使用子查询仅查找按DATETIME排序的事件100,101。
- 按EVENT_ID分组，使用count()查找重复项。
...但我怀疑这不能折叠结果，因为分组会忽略排序，
...而且我不知道如何获取两个（或多个！）对应的DATETIME值。

在MySQL中是否可能找到此内容？我可以在PHP中处理此问题，但我宁愿避免这种情况。

- Barry Staes

选择所有 event_id = 100 的行，且下一行没有值为 101，这是可以实现的。 - Goikiu

@Goikiu 这就是问题所在。语法应该是什么？ - Barry Staes

为了正确使用语法，我们需要一些解释。1）ID是唯一的吗？您可以有重复的ID吗？是否基于顺序排列？ - Goikiu

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- fancyPants · Accepted Answer

为了方便，我的示例中所有行都具有相同的日期时间值。

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 |
+----+----------+---------------------+----------+---------------------+