GROUP BY用于SQL中的连续行

SELECt  MIN(id) AS id, MIN(ts) AS ts, MIN(state) AS state, COUNT(*) cntFROM    (        SELECt  @r := @r + (@state != state) AS gn,     @state := state AS sn,     s.*        FROM    (     SELECT  @r := 0,  @state := 0     ) vars,     t_state s        ORDER BY     ts        ) qGROUP BY        gn

用于测试的表创建脚本：

CREATE TABLE t_state (id INT NOT NULL PRIMARY KEY, state INT NOT NULL, ts DATETIME NOT NULL);INSERTINTO  t_statevalUES(12,   1,      '2009-07-16 10:00'),(45,   2,      '2009-07-16 13:00'),(67,   2,      '2009-07-16 14:40'),(77,   1,      '2009-07-16 15:00'),(89,   1,      '2009-07-16 15:30'),(99,   1,      '2009-07-16 16:00');