将插入触发器设置为将最大值存储在另一列中

Question

这就是我的桌子的外观：

student_id	object_id	总数	最大
101	1	6
102	2	5
103	1	9
101	1	10 101 1 10
103	2	2
104	1	7

我希望“ max ”列在总计插入。

预期输出：

student_id	object_id	总数	最大
101	1	10	10
102	2	5	7
103	1	9	10
101	1	8	10
103	2	2	7
104	1	7	10

我想为此创建一个触发器。

这是我的选择陈述，可以正常工作，但是如何将其放在扳机中？

WITH CTE AS (SELECT `subject_id`,MAX(`total`) AS MaxTotal
FROM results
GROUP BY `subject_id`
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.`subject_id` = CTE.`subject_id`;

我这样做了，但我有很多错误

CREATE TRIGGER `max_score_before_INSERT` BEFORE INSERT ON `results`
FOR EACH ROW 
SET NEW.max = (WITH CTE AS (SELECT `subject_id`,MAX(`NEW.total`) AS MaxTotal
FROM results
GROUP BY `subject_id`
)
SELECT results.*,CTE.MaxTotal
FROM results
JOIN CTE ON results.`subject_id` = CTE.`subject_id`
);

Answer 1

在触发类型之前，您不能在内引用“ new ”。你可能想先
插入行，然后在触发类型之后使用更新列：

CREATE TRIGGER `max_score_before_INSERT` AFTER INSERT ON `results`
FOR EACH ROW 
UPDATE     <your_table_name>
INNER JOIN (SELECT subject_id, 
                   MAX(total) AS total_max
            FROM <your_table_name>
            GROUP BY subject_id) cte
        ON <your_table_name>.subject_id = cte.subject_id
SET <your_table_name>.max = cte.total_max;

请注意，此方法也会更新上一列也已经存在的行。如果不是，那么您可以在更新语句中使用条件：

CREATE TRIGGER `max_score_before_INSERT` AFTER INSERT ON `results`
FOR EACH ROW 
UPDATE     <your_table_name>
INNER JOIN (SELECT subject_id, 
                   MAX(total) AS total_max
            FROM <your_table_name>
            GROUP BY subject_id) cte
        ON <your_table_name>.subject_id = cte.subject_id
SET <your_table_name>.max = cte.total_max;
WHERE <your_table_name>.student_id = NEW.student_id
  AND <your_table_name>.subject_id = NEW.subject_id;