如何计算mysql查询结果

dsf9zpds  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(233)

例如,我有一个mysql表作为流:

| ID | name   | number | date       |
| -- | ------ | ------ | ---------- |
| 1  | apple  | 2      | 2018-04-10 |
| 2  | orange | 3      | 2018-04-10 |
| 3  | apple  | 1      | 2018-04-11 |
| 4  | orange | 5      | 2018-04-11 |

我想用一个查询来比较苹果和橘子在白天的变化。
大约在2018-04-10到2018-04-11之间,苹果的变化值是-1,橙色是3

m1m5dgzv

m1m5dgzv1#

你需要自我加入。
sql演示感谢@terry

SELECT t1.name, 
        t1.date as today, 
        t2.date as yesterday, 
        t1.number as today_value,
        t2.number as yesterday_value,
        t1.number - COALESCE(t2.number, t1.number) as difference

 FROM yourTable t1
 LEFT JOIN yourTable t2
   ON t1.date = DATE_ADD( t2.date, INTERVAL 1 DAY)
  AND t1.name = t2.name
 ORDER BY  t1.name, t1.date

我添加了一个条件,所以如果你没有前一天考虑没有任何变化,所以差异是0。
输出

|   name |      today |  yesterday | today_value | yesterday_value | difference |
|--------|------------|------------|-------------|-----------------|------------|
|  apple | 2018-04-10 |     (null) |           2 |          (null) |          0 |
|  apple | 2018-04-11 | 2018-04-10 |           1 |               2 |         -1 |
|  apple | 2018-04-12 | 2018-04-11 |          10 |               1 |          9 |
| orange | 2018-04-10 |     (null) |           3 |          (null) |          0 |
| orange | 2018-04-11 | 2018-04-10 |           5 |               3 |          2 |
| orange | 2018-04-12 | 2018-04-11 |          50 |               5 |         45 |
| orange | 2018-04-15 |     (null) |          10 |          (null) |          0 |

相关问题