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

问题描述

我需要使用前一天的最新日期减去MySQL中的两行：

启动表：

 股票日期价格
 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 
  

这是结果表：

 股票日期价格变动
 GOOG 2012-05-21 30 
 APPL 2012-05-21 37 
 FB 2012-05-21 -6 
  

现在我每个公司只有两个日期，但是如果您的查询可以处理任意数量的日期，则奖励会提高。

解决方案

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

  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股票，MIN（日期）minDate 
 FROM表名
 GROUP BY Stock 
）b ON a.stock = b.stock AND 
 a.date = b.minDate 
）minList ON maxList.stock = minList.stock 
  

SQLFiddle Demo

<看看你的最后一句话：现在我每个公司只有两个日期，但是如果你的查询有奖励upvotes可以处理任意数量的日期。如果您有这样的记录，该怎么办？

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

会是什么结果？

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.

解决方案

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?

这篇关于SQL根据日期和另一列减去两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！