我需要使用上一个日期的最新日期在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 34This is the result table:
Stock Date Price Change GOOG 2012-05-21 30 APPL 2012-05-21 37 FB 2012-05-21 -6Right 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.stockSQLFiddle演示
更新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.stockSQLFiddle 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 42what would be its result?
更多推荐
发布评论