MYSQL 返回选定行和之前行的数据

Question

我有一个表(price_history)，显示给定产品的价格历史记录。我想进行一个查询，显示当前价格(最近日期)和之前的价格(最近日期之前的日期)，因此本质上是旧价格和新价格。这是一个示例表:

| historyid | productcode | optionid | price | date       |
+-----------+-------------+----------+-------+------------+
|      9635 |      254256 |        1 | 42.95 | 1286494361 |
|      9690 |      254256 |        1 | 35.00 | 1286495188 |
|      9727 |      254256 |        1 | 47.95 | 1287529777 |
|      9728 |      254256 |        1 | 40.00 | 1288902624 |

这是我想要得到的:

| productcode | optionid | Old price |  New Price |  Date      |
+-------------+----------+-----------+------------+------------+
|      254256 |         1|     47.95 |  40.00     | 1288902624 |

我似乎不知道如何获得我需要的结果。任何帮助将不胜感激。谢谢

Answer 1

使用以下数据(适当格式化日期):

+-----------+-------------+----------+-------+------------+
| historyid | productcode | optionid | price | validdate  |
+-----------+-------------+----------+-------+------------+
|      9660 |      254256 |        1 | 35.00 | 2013-09-14 |
|      9727 |      254256 |        1 | 47.95 | 2013-09-15 |
|      9728 |      254256 |        1 | 40.00 | 2013-09-16 |
|      9635 |      254256 |        1 | 42.95 | 2013-09-13 |
+-----------+-------------+----------+-------+------------+

要获取最新日期的价格和前一天的价格，请使用以下命令:

select a.*, b.price as oldprice
from testing a 
left join testing b 
    on a.validdate = adddate(b.validdate, 1) 
order by a.validdate desc
limit 0, 1;

+-----------+-------------+----------+-------+------------+----------+
| historyid | productcode | optionid | price | validdate  | oldprice |
+-----------+-------------+----------+-------+------------+----------+
|      9728 |      254256 |        1 | 40.00 | 2013-09-16 |    47.95 |
+-----------+-------------+----------+-------+------------+----------+