MySQL更新顺序与来自同一个表的值

Question

我需要在表格中实现运行总和。这通常由代码完成，但我需要更新现有数据。在网上搜索后，我知道 MySQL 不允许更新从同一张表的子查询派生的值，但我也看到了克服此限制的技巧。不幸的是，我所看到的都没有满足我的需求。所以这就是表 atable 最终应该是这样的:

ID  amount    asum
------------------
1    10.00   10.00
2    20.00   30.00
3    -5.00   25.00
4   100.00  125.00
....

ID 列是自动递增的，因此这些值是按顺序排列的。 amount中的值来自UI，asum列需要按照ID的顺序计算。因此我手动更新了第一行并尝试了类似

UPDATE atable t 
SET t.asum = t.amount + (SELECT s.asum FROM atable s WHERE s.ID = t.ID - 1)
WHERE t.ID > 1; 

或者 asum 列是所有金额的总和，ID <= 当前 ID，所以我尝试了

UPDATE atable t
SET t.asum = (SELECT SUM(s.amount) FROM atable s WHERE s.ID <= t.ID);

这两个语句都会抛出错误 1093。有人对此有解决方案吗？

Answer 1

试试这个查询:

-- query wanted
UPDATE atable t CROSS JOIN (SELECT @s := 0) param
SET t.asum = (@s := (@s + t.amount));

下面是一个完整的demo。

SQL:

-- data
create table atable(ID int auto_increment primary key, 
    amount decimal(6,2), asum decimal(6,2) default 0.0);
insert into atable(amount) values
(10.00),(20.00),(-5.00),(100.00);
select * from atable;

-- query wanted
UPDATE atable t CROSS JOIN (SELECT @s := 0) param
SET t.asum = (@s := (@s + t.amount));

输出:

mysql> select * from atable;
+----+--------+------+
| ID | amount | asum |
+----+--------+------+
|  1 |  10.00 | 0.00 |
|  2 |  20.00 | 0.00 |
|  3 |  -5.00 | 0.00 |
|  4 | 100.00 | 0.00 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql>
mysql> -- query wanted
mysql> UPDATE atable t CROSS JOIN (SELECT @s := 0) param
    -> SET t.asum = (@s := (@s + t.amount));
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql>
mysql> select * from atable;
+----+--------+--------+
| ID | amount | asum   |
+----+--------+--------+
|  1 |  10.00 |  10.00 |
|  2 |  20.00 |  30.00 |
|  3 |  -5.00 |  25.00 |
|  4 | 100.00 | 125.00 |
+----+--------+--------+
4 rows in set (0.00 sec)