问题描述
我需要使用前一天的最新日期减去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
现在我每个公司只有两个日期,但是如果你的查询有奖励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根据日期和另一列减去两行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!