mysql - 当某些状态发生变化时，如何从日志表中获取时间戳？

我有一个在不同日期发生的状态更改列表：

create table log (
  date date not null,
  state int not null default 0
);

insert into log (date, state) values
  ('2015-06-01', '0'),
  ('2015-06-05', '1'),
  ('2015-06-09', '1'),
  ('2015-06-10', '0'),
  ('2015-06-11', '0'),
  ('2015-06-14', '1'),
  ('2015-06-16', '1'),
  ('2015-06-26', '1'),
  ('2015-06-27', '0'),
  ('2015-06-28', '0'),
  ('2015-06-30', '1');

我怎样才能得到状态改变的那对日期？
预期产量：

date 1,       date 2,       state 1,  state 2
'2015-06-01', '2015-06-05', 0,        1
'2015-06-09', '2015-06-10', 1,        0
'2015-06-11', '2015-06-14', 0,        1
'2015-06-26', '2015-06-27', 1,        0
'2015-06-28', '2015-06-30', 0,        1

最佳答案

状态更改的一个可能查询是：

select pair.date as "date 1", pair.nextdate as "date 2", pair.state as "state 1", log.state as "state 2"
  from
    (select cur.*, min(next.date) as nextdate
      from log as cur
      join log as next on cur.date < next.date
      group by cur.date, cur.state) as pair
    join log on log.date = pair.nextdate
  where pair.state <> log.state;

解释
通过在条件a.date < b.date下将log与自身连接，我们得到一个
一对一对的约会。

select * from log as cur
  join log as next on cur.date < next.date
  order by cur.date, next.date;


cur.date,     cur.state, next.date,    next.state
'2015-06-01', 0,         '2015-06-05', 1
'2015-06-01', 0,         '2015-06-09', 1
'2015-06-01', 0,         '2015-06-10', 0
'2015-06-01', 0,         '2015-06-11', 0
'2015-06-01', 0,         '2015-06-14', 1
'2015-06-01', 0,         '2015-06-16', 1
'2015-06-01', 0,         '2015-06-26', 1
'2015-06-01', 0,         '2015-06-27', 0
'2015-06-01', 0,         '2015-06-28', 0
'2015-06-01', 0,         '2015-06-30', 1
'2015-06-05', 1,         '2015-06-09', 1
'2015-06-05', 1,         '2015-06-10', 0

如果取next.date的最小值，我们将得到后面日期的时间戳
cur.date。

select cur.*, min(next.date) as "nextdate"
  from log as cur
  join log as next on cur.date < next.date
  group by cur.date, cur.state;

date,         state, nextdate
'2015-06-01', '0',   '2015-06-05'
'2015-06-05', '1',   '2015-06-09'
'2015-06-09', '1',   '2015-06-10'
'2015-06-10', '0',   '2015-06-11'
'2015-06-11', '0',   '2015-06-14'
'2015-06-14', '1',   '2015-06-16'
'2015-06-16', '1',   '2015-06-26'
'2015-06-26', '1',   '2015-06-27'
'2015-06-27', '0',   '2015-06-28'
'2015-06-28', '0',   '2015-06-30'

为了得到state的nextdate，我们再次加入log并过滤状态
更改（pair.state <> log.state）。

关于mysql - 当某些状态发生变化时，如何从日志表中获取时间戳？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30918937/