在 MySQL 查询中将两个子查询之间的差异显示为附加列

Question

我有两个表，一个包含主数据，另一个表包含历史值。

表 stocks

+----------+-------+-----------+
| stock_id | symbol| name      |    
+----------+-------+-----------+    
|        1 |  AAPL | Apple     |
|        2 |  GOOG | Google    |
|        3 |  MSFT | Microsoft |
+----------+-------+-----------+

表prices

+----------+-------+---------------------+
| stock_id | price | date                |    
+----------+-------+---------------------+    
|        1 |  0.05 | 2015-02-24 01:00:00 |
|        2 |  2.20 | 2015-02-24 01:00:00 |
|        1 |  0.50 | 2015-02-23 23:00:00 |
|        2 |  1.90 | 2015-02-23 23:00:00 | 
|        3 |  2.10 | 2015-02-23 23:00:00 |
|        1 |  1.00 | 2015-02-23 19:00:00 |
|        2 |  1.00 | 2015-02-23 19:00:00 | 
+----------+-------+---------------------+

我需要一个返回以下内容的查询：

+----------+-------+-----------+-------+
| stock_id | symbol| name      | diff  |  
+----------+-------+-----------+-------+    
|        1 |  AAPL | Apple     | -0.45 |
|        2 |  GOOG | Google    | 0.30  |
|        3 |  MSFT | Microsoft | NULL  |
+----------+-------+-----------+-------+

其中差异是从股票的最新价格中减去前一个价格的结果。如果特定股票存在一个或更少的价格，我应该得到 NULL。

我有以下查询返回最后价格和上一个价格，但我不知道如何连接所有内容

/* last */
SELECT price 
FROM prices
WHERE stock_id = '1'
ORDER BY date DESC 
LIMIT 1
/* previous */
SELECT price 
FROM prices
WHERE stock_id = '1'
ORDER BY date DESC 
LIMIT 1,1

使用 MySQL 5.5

Answer 1

这将返回预期的结果集：

SELECT stock_id, symbol, name, 
       SUM(CASE WHEN row_number = 1 THEN price END) - 
       SUM(CASE WHEN row_number = 2 THEN price END) AS diff
FROM (
   SELECT @row_number:=CASE WHEN @stock=stock_id THEN @row_number+1 
                            ELSE 1 
                       END AS row_number,
          @stock:=stock_id AS stock_id,
          price, date, symbol, name
   FROM (SELECT p.stock_id, s.symbol, s.name, p.price, p.date
         FROM prices AS p
         INNER JOIN stocks AS s ON p.stock_id = s.stock_id
         ORDER BY stock_id, date DESC) AS t
   ) u
GROUP BY u.stock_id

SQL 小提琴演示

Answer 2

这应该可以做到：

SELECT s1.symbol,
       s1.name,
       COALESCE ((SELECT price 
                    FROM prices p1
                   WHERE p1.stock_id = s1.stock_id
                   ORDER BY dateTime DESC 
                   LIMIT 1), 0) -
       COALESCE ((SELECT price       
                    FROM prices p2
                   WHERE p2.stock_id = s1.stock_id
                   ORDER BY dateTime DESC 
                   LIMIT 1,1), 0) AS diff
  FROM stocks s1;