按日期展示MySQL SUM数据的触发器实现求助

我有一张包含p_code、k_code、score、date字段的数据表my_table，数据如下：

需要按date和p_code分组统计score的总和，得到如下结果：

之前尝试的SQL语句无法得到正确结果：

SELECT p_code, SUM(score) AS total_score, date FROM my_table GROUP BY p_code

现在需要用触发器实现该统计需求。

1. 先纠正原SQL问题 #

你的原SQL错误在于GROUP BY子句只指定了p_code，但查询中包含了非聚合字段date，不符合SQL分组规则。正确的查询语句直接按p_code和date分组即可得到目标结果：

SELECT 
    p_code, 
    SUM(score) AS total_score, 
    date 
FROM my_table 
GROUP BY p_code, date 
ORDER BY date, p_code;

2. 用触发器实现自动统计（维护统计表） #

如果需要实时维护统计结果（而非每次查询计算），可以创建统计表，通过触发器在原表数据变化时自动更新统计表：

步骤1：创建统计表 #

先创建存储统计结果的表p_score_summary：

CREATE TABLE p_score_summary (
    p_code VARCHAR(10) NOT NULL,
    date DATE NOT NULL,
    total_score INT NOT NULL DEFAULT 0,
    PRIMARY KEY (p_code, date)
);

步骤2：初始化统计数据 #

将现有数据的统计结果插入统计表：

INSERT INTO p_score_summary (p_code, date, total_score)
SELECT p_code, date, SUM(score)
FROM my_table
GROUP BY p_code, date;

步骤3：创建INSERT触发器 #

原表插入新数据时，同步更新统计表：

DELIMITER //
CREATE TRIGGER trg_my_table_insert
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
    INSERT INTO p_score_summary (p_code, date, total_score)
    VALUES (NEW.p_code, NEW.date, NEW.score)
    ON DUPLICATE KEY UPDATE total_score = total_score + NEW.score;
END //
DELIMITER ;

步骤4：创建UPDATE触发器 #

原表的score、p_code或date字段更新时，同步修正统计表：

DELIMITER //
CREATE TRIGGER trg_my_table_update
AFTER UPDATE ON my_table
FOR EACH ROW
BEGIN
    -- 减去旧数据的分值
    UPDATE p_score_summary
    SET total_score = total_score - OLD.score
    WHERE p_code = OLD.p_code AND date = OLD.date;
    
    -- 加上新数据的分值（若p_code/date变化则插入新记录）
    INSERT INTO p_score_summary (p_code, date, total_score)
    VALUES (NEW.p_code, NEW.date, NEW.score)
    ON DUPLICATE KEY UPDATE total_score = total_score + NEW.score;
END //
DELIMITER ;

步骤5：创建DELETE触发器 #

原表删除数据时，更新统计表：

DELIMITER //
CREATE TRIGGER trg_my_table_delete
AFTER DELETE ON my_table
FOR EACH ROW
BEGIN
    UPDATE p_score_summary
    SET total_score = total_score - OLD.score
    WHERE p_code = OLD.p_code AND date = OLD.date;
    
    -- 可选：总分变为0时删除该统计记录
    DELETE FROM p_score_summary
    WHERE p_code = OLD.p_code AND date = OLD.date AND total_score = 0;
END //
DELIMITER ;

之后，my_table的数据发生增删改时，p_score_summary会自动维护最新统计结果，直接查询该表即可得到目标格式：

SELECT p_code, total_score, date FROM p_score_summary ORDER BY date, p_code;

内容的提问来源于stack exchange，提问作者Identic 7