mysql - GROUP BY 用于 SQL 中的连续行

Question

给出下表:

ID   State  Date12   1      2009-07-16 10:0045   2      2009-07-16 13:0067   2      2009-07-16 14:4077   1      2009-07-16 15:0089   1      2009-07-16 15:3099   1      2009-07-16 16:00

Question:
How can i GROUP by the field "State", while still maintaining the borders between the state changes?

SELECT MIN(ID) AS ID, State, MIN(Date) AS Date, COUNT(ID) AS Count
FROM table GROUP BY State

结果如下:

ID   State  Date              Count12   1      2009-07-16 10:00  445   2      2009-07-16 13:00  2

但这是所需的输出:

ID   State  Date              Count12   1      2009-07-16 10:00  145   2      2009-07-16 13:00  277   1      2009-07-16 15:00  3

这在 SQL 中可能吗？到目前为止我没有找到解决方案...

Answer 1

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');