GROUP BY用于SQL中的连续行

一尘不染

GROUP BY用于SQL中的连续行

mysql

给出下表：

**身份证状态日期**
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

问题：
在保持状态变化之间的边界的同时，如何在“状态”字段中进行分组？

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

结果如下：

**ID状态日期计数**
12 1 2009-07-16 10:00 4
45 2 2009-07-16 13:00 2

但这是预期的：

**ID状态日期计数**
12 1 2009-07-16 10:00 1
45 2 2009-07-16 13:00 2
77 1 2009-07-16 15:00 3

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

阅读 488

2020-05-17

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

2020-05-17