php 找到行mysql_php – MySQL查找行

在我的示例中,所有行都具有相同的日期时间值.

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 |

+----+----------+---------------------+----------+---------------------+