SQL根据日期和另一列减去两行(SQL subtract two rows based on date and another column)

我需要使用上一个日期的最新日期在MySQL中减去两行：

起始位置：

Stock Date Price GOOG 2012-05-20 402 GOOG 2012-05-21 432 APPL 2012-05-20 553 APPL 2012-05-21 590 FB 2012-05-20 40 FB 2012-05-21 34

这是结果表：

Stock Date Price Change GOOG 2012-05-21 30 APPL 2012-05-21 37 FB 2012-05-21 -6

现在我每个公司只有两个日期，但如果你的查询可以处理任意数量的日期，奖金就会增加。

I need to subtract two rows in MySQL by using the most recent date from the previous date:

Starting table:

Stock Date Price GOOG 2012-05-20 402 GOOG 2012-05-21 432 APPL 2012-05-20 553 APPL 2012-05-21 590 FB 2012-05-20 40 FB 2012-05-21 34

This is the result table:

Stock Date Price Change GOOG 2012-05-21 30 APPL 2012-05-21 37 FB 2012-05-21 -6

Right now I just have two dates per company, but bonus upvotes if your query can handle any number of dates.

最满意答案

我所做的是我得到两个单独的查询来获得每个股票的最大日期和最短日期。 尝试这个，

SELECT maxList.stock, maxList.Date, (maxlist.Price - minlist.Price) totalPrice FROM ( SELECT a.* FROM tableName a INNER JOIN ( SELECT Stock, MAX(date) maxDate FROM tableName GROUP BY Stock ) b ON a.stock = b.stock AND a.date = b.maxDate ) maxList INNER JOIN ( SELECT a.* FROM tableName a INNER JOIN ( SELECT Stock, MIN(date) minDate FROM tableName GROUP BY Stock ) b ON a.stock = b.stock AND a.date = b.minDate ) minList ON maxList.stock = minList.stock

SQLFiddle演示

更新1

看到你的最后一句： Right now I just have two dates per company, but bonus upvotes if your query can handle any number of dates. 如果你有这样的记录怎么办？

FB 2012-05-20 40 FB 2012-05-21 34 FB 2012-05-22 42

会有什么结果？

What I've done was I get two separate queries to get each stock's maximum date and minimum date. Try this,

SELECT maxList.stock, maxList.Date, (maxlist.Price - minlist.Price) totalPrice FROM ( SELECT a.* FROM tableName a INNER JOIN ( SELECT Stock, MAX(date) maxDate FROM tableName GROUP BY Stock ) b ON a.stock = b.stock AND a.date = b.maxDate ) maxList INNER JOIN ( SELECT a.* FROM tableName a INNER JOIN ( SELECT Stock, MIN(date) minDate FROM tableName GROUP BY Stock ) b ON a.stock = b.stock AND a.date = b.minDate ) minList ON maxList.stock = minList.stock

SQLFiddle Demo

UPDATE 1

seeing your last sentence: Right now I just have two dates per company, but bonus upvotes if your query can handle any number of dates. What if you have records like this?

FB 2012-05-20 40 FB 2012-05-21 34 FB 2012-05-22 42

what would be its result?