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-1开发者_运维知识库0-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
SELECT
a.price as price1
, IFNULL(b.price,'(no data)') as price2
, (a.price - IFNULL(b.price,0)) as difference
FROM table1 a
LEFT JOIN table2 b ON (a.`item name` = b.`item name`)
GROUP BY a.`item name`
HAVING IFNULL(b.`date`,'') = MAX(IFNULL(b.`date`,'')
Here's how it works.
It selects data from 2 tables: All data from table1 and matching data from table2.
If it cannot find matching data from table2 it will substitute null
values in place of the missing rows. (left join
)
Then it groups (group by
) rows together based on table1.item name
.
This combines multiple rows per item.
The having
clause fixes this by only selecting the newest date rows from table2.
A small correction is build into the select
and having
clauses to deal with the case when there is no data in table2 to match table1.
Your query should be:
SELECT
s.closing as price1
, IFNULL(sh.closing,'(no data)') as price2
, (s.closing - IFNULL(sh.closing,0)) as difference
FROM stocks s
LEFT JOIN stockhistory sh ON (s.symbol = sh.symbol)
GROUP BY s.symbol
HAVING IFNULL(sh.edate,'') = MAX(IFNULL(sh.edate,'')
LIMIT 30 OFFSET 0;
精彩评论