SELECT MIN(id) AS id, MIN(ts) AS ts, MIN(state) AS state, COUNT(*) cnt
FROM (
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
) q
GROUP BY
gn
用于测试的表创建脚本：
CREATE TABLE t_state (id INT NOT NULL PRIMARY KEY, state INT NOT NULL, ts DATETIME NOT NULL);
INSERT
INTO t_state
VALUES
(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');
602392714
清零编程群