mysql - MySQL 计算行之间的差异

Question

我有一个这样的表:

+----------+-----+--------+----+---------+-----+--------+ 
| Week     | #X  | Diff_X | #Y | Diff_Y  | #Z  | Diff_Z |
+----------+-----+--------+----+---------+-----+--------+ 
| 22       | 25  | Null   | 13 | NULL    |  0  | NULL   |
| 23       | 25  | Null   | 18 | NULL    |  12 | NULL   |
| 24       | 39  | Null   | 15 | NULL    |  16 | NULL   |
+----------+-----+--------+----+---------+-----+--------+ 

每周都会更新，所以每周 1 行。

我将如何继续计算每周之间每个值之间的差异？并将它们放入指定的栏目中。

所以想要的结果应该是这样的:

+----------+-----+--------+----+---------+-----+--------+ 
| Week     | #X  | Diff_X | #Y | Diff_Y  | #Z  | Diff_Z |
+----------+-----+--------+----+---------+-----+--------+ 
| 22       | 25  | 0      | 13 | 0       |  2  | 0      |
| 23       | 25  | 0      | 18 | 5       |  12 | 10     |
| 24       | 39  | 14     | 15 | -3      |  16 | 4      |
+----------+-----+--------+----+---------+-----+--------+ 

我完全不知道该怎么做，所以任何形式的帮助将不胜感激!

Answer 1

UPDATE mytable t1
INNER JOIN mytable t2 ON t1.Week + 1 = t2.Week
SET t2.`Diff_X` = t2.`#X` - t1.`#X`,
    t2.`Diff_Y` = t2.`#Y` - t1.`#Y`,
    t2.`Diff_Z` = t2.`#Z` - t1.`#Z`