MySQL触发器总和

Question

我想帮助一个前更新触发器，有条件地求和金额列，其中日和id匹配，只显示总和，其中变量是' total '。

id | day | variable | amount
-: | :------ | :------- | :-----
1 | Monday | Total | null
1 | Monday | null | 1
1 | Monday | null | 2
1 | Monday | null | 3
1 | Tuesday | Total | null
1 | Tuesday | null | 1
1 | Tuesday | null | 2
1 | Tuesday | null | 3
2 | Monday | Total | null
2 | Monday | null | 1
2 | Monday | null | 2
2 | Monday | null | 3
2 | Tuesday | Total | null
2 | Tuesday | null | 1
2 | Tuesday | null | 2
2 | Tuesday | null | 3

是否有一种方法来控制sum函数，使它不会更新每个总数，除非一个相关的值已经更新?也就是说，我不希望星期一的总数在星期二的行或另一个ID内的值发生变化时重新计算。

小提琴:https://dbfiddle.uk/?rdbms=mysql_8.0&小提琴= fc3939aa508002dab7f2af45611717cf

Answer 1

请注意，根据文档，MySQL对在同一个触发器中更新同一表中的其他行有限制。

存储函数或触发器不能修改已被调用该函数或触发器的语句使用的表(用于读写)。

如果下面的SQL语句可以工作

UPDATE tb1 t1
JOIN (
SELECT id, day, SUM(amount) as total
FROM tb1 t
WHERE id = 1 AND day = 'Monday' AND variable is NULL
GROUP BY id, day
) t2 ON t1.id = t2.id AND t1.day = t2.day AND t1.variable = 'Total'
SET t1.amount = t2.total;

那么它在触发器内部的实现将抛出一个错误

FROM子句中不能指定目标表'tb1'进行更新

但是你可以不使用触发器创建一个视图

SELECT 
id, day, variable,
CASE WHEN variable = 'Total' 
THEN (
SELECT SUM(amount) FROM tb1 t1 
WHERE t1.id = t.id AND t1.day = t.day AND t1.variable IS NULL
) 
ELSE amount
END AS amount
FROM tb1 t  

或者，甚至可以完全删除具有Total的所有行，并使用WITH ROLLUP子句检索它们。

SELECT 
id, 
day,
CASE WHEN amount IS NULL THEN 'Total' END AS variable,
SUM(amount) AS amount
FROM tb1
GROUP BY id, day, amount
WITH ROLLUP
HAVING id IS NOT NULL AND day IS NOT NULL

,db&lt的在小提琴