SQL，如何查找列值的更改

Question

我有一张表格，有两列，一个是日期（DateTime类型），另一个是数值（INT类型，值为1、2、3其中之一）

这个表格的数据大致如下：

Date                     , Value
---------------------------------------
2016-01-01 10:34:00                1
2016-01-01 10:35:00                1
2016-01-01 10:36:00                1
2016-01-01 10:37:00                1
2016-01-01 10:38:00                1
2016-01-01 10:39:00                3
2016-01-01 10:40:00                3
2016-01-01 10:41:00                2
2016-01-01 10:42:00                2
2016-01-01 10:43:00                2
2016-01-01 10:44:00                1
2016-01-01 10:45:00                1
2016-01-01 10:46:00                1
2016-01-01 10:47:00                1

我需要一个SQL查询语句（尤其是在MySQL中）来生成输出：

Start Date              ,  End Date            , value
---------------------------------
2016-01-01 10:34:00     , 2016-01-01 10:38:00  , 1
2016-01-01 10:39:00     , 2016-01-01 10:40:00  , 3
2016-01-01 10:41:00     , 2016-01-01 10:43:00  , 2
2016-01-01 10:44:00     , 2016-01-01 10:47:00  , 1

这是可能的吗？ 请帮忙。谢谢。

- Hamid Reza Arzaghi

---

你是否一直希望按照日期的顺序排列，就像你的示例中那样？ - Windle

Answer 1

最快的方法是使用变量。您需要定义一个分组参数，每当值发生变化时就会改变：

select min(date), max(date), value
from (select t.*,
             (@grp := if(@v = value, @grp,
                         if(@v := value, @grp + 1, @grp + 1)
                        )
             ) as grp
      from t cross join
           (select @grp := 0, @v := -1) params
      order by date
     ) t
group by grp, value;

- Gordon Linoff

Answer 2

这里再来一个

SELECT min(my_date), max(my_date), val
FROM (
  SELECT @nr := IF(@old_val = val, @nr, @nr := @nr +1) AS nr
  , @old_val := val
  , t.* 
  FROM onoff t
    ,(SELECT @nr:=0, @old_val='') AS tmp
  ORDER BY my_date
  ) AS t1
  GROUP BY nr
  ORDER BY min(my_date);

表格

CREATE TABLE `onoff` (
  `my_date` datetime NOT NULL,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`my_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `onoff` (`my_date`, `val`)
VALUES
    ('2016-01-01 10:34:00', 1),
    ('2016-01-01 10:35:00', 1),
    ('2016-01-01 10:36:00', 1),
    ('2016-01-01 10:37:00', 1),
    ('2016-01-01 10:38:00', 1),
    ('2016-01-01 10:39:00', 3),
    ('2016-01-01 10:40:00', 3),
    ('2016-01-01 10:41:00', 2),
    ('2016-01-01 10:42:00', 2),
    ('2016-01-01 10:43:00', 2),
    ('2016-01-01 10:44:00', 1),
    ('2016-01-01 10:45:00', 1),
    ('2016-01-01 10:46:00', 1),
    ('2016-01-01 10:47:00', 1);

- Bernd Buffen