MySQL - 从列相同的第 1 行中减去第 2 行

Question

我正在创建一个临时表，当前提取今天和昨天的值。临时表如下所示:

|DateTime                |company 1 |company 2 |
+------------------------+----------------------+
|2013-10-03 14:40:00     | 182475   |  110271  |
|2013-10-02 14:10:00     | 182086   |  110261  |

我需要做的是选择今天的值，以及今天和昨天的差值。根据以上数据，输出将如下所示:

Company 1   | Company 1 difference | Company 2|  Company 2 difference
+-----------+----------------------+----------+--------------------------
182475      |         389          | 110271   |          10

我不知道如何指定从哪一行中减去哪一行，因为没有自动增量字段，并且 DateTime 字段每天都会略有不同。

感谢您的帮助!

Answer 1

SELECT today.DateTime as DateTime
  , today.Company1, today.Company1 - COALESCE(yesterday.Company1,0) AS Company1Diff
  , today.Company2, today.Company2 - COALESCE(yesterday.Company2,0) AS Company2Diff
FROM tbl AS today
LEFT JOIN tbl AS yesterday
ON DATEDIFF(today.DateTime, yesterday.DateTime) = 1
ORDER BY today.DateTime ASC