减去MySQL表中的值 [英] Subtracting values in MySQL Table

问题描述

我有两个不同表格的价格，并希望减去它们（目前的价格 - 最后一天的价格），并以DESC格式订购。我想知道是否可以使用单个MySQL命令完成。

表结构

 code>表1 
 id |项目名称|日期|价格
 1 | alpha | 2011-10-05 | 10 
 2 | beta | 2011-10-05 | 12 
 3 | gamma | 2011-10-05 | 14 
 
表2 
 id |项目名称|日期|价格
 1 | alpha | 2011-10-04 | 8 
 2 | beta | 2011-10-04 | 10 
 3 | gamma | 2011-10-04 | 12 
 4 | alpha | 2011-10-03 | 4 
 5 | beta | 2011-10-03 | 6 
 6 | gamma | 2011-10-03 | 8 
  

解决方案

  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差异
 FROM table1 
 LEFT JOIN table2 ON table1.id = table2.id AND table1.`Date`  -  INTERVAL 1 DAY = table2.`Date `
 ORDER BY差异DESC 
  

这个查询没有什么特别之处， ve使用了LEFT JOIN。我相信如果昨天的记录率不可用，最后三列将包含NULL。输出：

  id |项目名称| CurrDate | CurrPrice | PrevDate | PrevPrice |差异
 2 | beta | 2011-10-05 | 12 | 2011-10-04 | 10 | 2 
 3 | gamma | 2011-10-05 | 14 | 2011-10-04 | 12 | 2 
 1 | alpha | 2011-10-05 | 10 | 2011-10-04 | 8 | 2 
  

I have prices in two different tables and want to subtract them (current price-last day price) and ORDER them in DESC form. I was wondering if it can be done using a single MySQL command.

Table Structure

Table 1
id | Item Name | Date       | Price
 1 | alpha     | 2011-10-05 | 10
 2 | beta      | 2011-10-05 | 12
 3 | gamma     | 2011-10-05 | 14 

Table 2
id | Item Name | Date       | Price
 1 | alpha     | 2011-10-04 | 8
 2 | beta      | 2011-10-04 | 10
 3 | gamma     | 2011-10-04 | 12
 4 | alpha     | 2011-10-03 | 4
 5 | beta      | 2011-10-03 | 6
 6 | gamma     | 2011-10-03 | 8

解决方案

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 Difference
FROM table1
LEFT JOIN table2 ON table1.id = table2.id AND table1.`Date` - INTERVAL 1 DAY = table2.`Date`
ORDER BY Difference DESC

There is nothing special about this query except the way I've used the LEFT JOIN. I believe if yesterday's rates for a record are not available, the the last three columns would contain NULL. Output:

id | Item Name | CurrDate   | CurrPrice | PrevDate   | PrevPrice | Difference
2  | beta      | 2011-10-05 | 12        | 2011-10-04 | 10        | 2
3  | gamma     | 2011-10-05 | 14        | 2011-10-04 | 12        | 2
1  | alpha     | 2011-10-05 | 10        | 2011-10-04 | 8         | 2

这篇关于减去MySQL表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！