时间戳

首页 > 解决方案 > MySQL 触发器总和

mysql - MySQL 触发器总和

问题描述

我想要一个更新前触发器的帮助，它有条件地对日期和 id 匹配的金额列进行求和，并且只显示变量为“总计”的总和。

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 函数，以便它不会更新每个总数，除非已更新关联值？IE 我不希望在周二行内的值发生变化或另一个 ID 发生变化时重新计算周一总数。

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

标签： mysql

解决方案

请注意，根据文档，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

或者，甚至可以使用 a 完全删除所有行并使用子句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<>小提琴

推荐阅读