MySQL-将定时事件行转换为状态范围

Question

假设一所房子每隔一段时间就会被粉刷一次，我在一个简单的表中跟踪变化（"粉刷"事件）：

 _______________________
/ PaintDate | NewColor  
|-----------|-----------|
| 1/2/2012  |  Red      |
| 3/5/2013  |  Blue     |
| 9/9/2013  |  Green    |
___________|___________/

是否有SELECT语句可以为我提供一个特定颜色的房屋日期范围的表格？

期望输出：

 _______________________________
/  From    |   To     |  Color  
|----------|----------|---------|
| 1/2/2012 | 3/5/2013 |   Red   |
| 3/5/2013 | 9/9/2013 |   Blue  |
| 9/9/2013 | NULL     |   Green | -- Not repainted yet, the date in the 'To' column should be NULL
__________|__________|_________/

Answer 1

你可以使用这个查询

SELECT 
  p.PaintDate AS `From`,
  l.PaintDate AS `TO`,
  p.NewColor AS `Color` 
FROM
  paint AS p 
  LEFT JOIN 
    (SELECT * FROM paint LIMIT 1, 18446744073709551615) AS l   
    ON l.PaintDate = (SELECT PaintDate FROM paint WHERE PaintDate > p.PaintDate ORDER BY PaintDate ASC LIMIT 1)

输出

From       |    TO          | Color
-------------------------------
2012-02-01 |    2012-05-03  | Red
2012-05-03 |    2013-09-09  | Blue
2013-09-09 |    (NULL)      | Green

演示

上面的查询可以像这个一样得到改进

SELECT 
  p.PaintDate AS `From`,
  l.PaintDate AS `TO`,
  p.NewColor AS `Color` 
FROM
  paint AS p 
  LEFT JOIN paint AS l ON l.PaintDate = (SELECT PaintDate FROM paint WHERE PaintDate > p.PaintDate ORDER BY PaintDate ASC  LIMIT 1)

演示

Answer 2

SELECT  aa.PaintDate `FROM`, bb.PaintDate `TO`,
        aa.NewColor
FROM    
        (
            SELECT  @row:=@row+1 as RankNo,
                    a.PaintDate,
                    a.NewColor
            FROM    tableName a, (SELECT @row:=0) b
            ORDER   BY a.PaintDate
        ) aa
        LEFT JOIN
        (
            SELECT  @row2:=@row2+1 as RankNo,
                    a.PaintDate
            FROM    tableName a, (SELECT @row2:=0) b
            ORDER   BY a.PaintDate
        ) bb ON aa.RankNo+1 = bb.RankNo

• SQLFiddle演示