SELECt table1.id, table1.`Item Name`,table1.`Date` AS CurrDate, table1.Price AS CurrPrice,table2.`Date` AS PrevDate, table2.Price AS PrevPrice,table1.Price - table2.Price AS DifferenceFROM table1LEFT JOIN table2 ON table1.id = table2.id AND table1.`Date` - INTERVAL 1 DAY = table2.`Date`ORDER BY Difference DESC
除了我使用LEFT JOIN的方式外，此查询没有什么特别的。我相信，如果无法提供昨天的记录价格，则最后三列将包含NULL。输出：
id | Item Name | CurrDate | CurrPrice | PrevDate | PrevPrice | Difference2 | beta | 2011-10-05 | 12 | 2011-10-04 | 10 | 23 | gamma | 2011-10-05 | 14 | 2011-10-04 | 12 | 21 | alpha | 2011-10-05 | 10 | 2011-10-04 | 8 | 2
