SQL中重复记录聚合及每日新增数据自动求和方案咨询

一、手动聚合查询（用于验证或一次性处理） #

按Agent ID和Agent Name分组，对各月份字段求和（空值视为0），并保留非空的Work Record ID：

SELECT
  `Agent ID`,
  `Agent Name`,
  MAX(`Work Record ID`) AS `Work Record ID`, -- 取非空的记录ID，若同一Agent有多个不同ID需调整规则
  SUM(COALESCE(Jan, 0)) AS Jan,
  SUM(COALESCE(Feb, 0)) AS Feb,
  SUM(COALESCE(Mar, 0)) AS Mar,
  SUM(COALESCE(Apr, 0)) AS Apr
  -- 后续月份字段依此类推
FROM your_table_name
GROUP BY `Agent ID`, `Agent Name`;

说明：COALESCE函数将空值转换为0，避免求和时忽略空值；MAX(Work Record ID)确保取到非空的记录ID（空值会被聚合函数忽略）。

二、自动执行方案（适配每日新增数据） #

方案1：触发器（实时自动更新） #

适合需要实时同步聚合结果的场景，步骤如下：

1. 创建聚合结果表（结构与期望结果一致）：

CREATE TABLE agent_monthly_summary (
  `Agent ID` VARCHAR(20) PRIMARY KEY,
  `Agent Name` VARCHAR(50),
  `Work Record ID` VARCHAR(20),
  Jan INT DEFAULT 0,
  Feb INT DEFAULT 0,
  Mar INT DEFAULT 0,
  Apr INT DEFAULT 0
  -- 后续月份字段依此类推
);

2. 创建插入触发器，每次原表新增数据时自动更新聚合表：

DELIMITER //
CREATE TRIGGER trg_update_summary AFTER INSERT ON your_table_name
FOR EACH ROW
BEGIN
  -- 更新已有Agent的聚合记录
  UPDATE agent_monthly_summary
  SET
    `Agent Name` = COALESCE(NEW.`Agent Name`, `Agent Name`),
    `Work Record ID` = COALESCE(NEW.`Work Record ID`, `Work Record ID`),
    Jan = Jan + COALESCE(NEW.Jan, 0),
    Feb = Feb + COALESCE(NEW.Feb, 0),
    Mar = Mar + COALESCE(NEW.Mar, 0),
    Apr = Apr + COALESCE(NEW.Apr, 0)
    -- 后续月份字段依此类推
  WHERE `Agent ID` = NEW.`Agent ID`;

  -- 若该Agent无聚合记录，插入新条目
  IF ROW_COUNT() = 0 THEN
    INSERT INTO agent_monthly_summary (
      `Agent ID`, `Agent Name`, `Work Record ID`, Jan, Feb, Mar, Apr
      -- 后续月份字段依此类推
    ) VALUES (
      NEW.`Agent ID`,
      NEW.`Agent Name`,
      NEW.`Work Record ID`,
      COALESCE(NEW.Jan, 0),
      COALESCE(NEW.Feb, 0),
      COALESCE(NEW.Mar, 0),
      COALESCE(NEW.Apr, 0)
      -- 后续月份字段依此类推
    );
  END IF;
END //
DELIMITER ;

方案2：定时任务（每日批量聚合） #

适合数据量较大、无需实时同步的场景，以MySQL事件调度器为例：

1. 开启事件调度器：

SET GLOBAL event_scheduler = ON;

2. 创建每日执行的聚合任务：

DELIMITER //
CREATE EVENT evt_daily_agent_summary
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:30:00' -- 每日凌晨30分执行
DO
BEGIN
  TRUNCATE TABLE agent_monthly_summary; -- 清空旧数据
  -- 重新生成聚合结果
  INSERT INTO agent_monthly_summary
  SELECT
    `Agent ID`,
    `Agent Name`,
    MAX(`Work Record ID`) AS `Work Record ID`,
    SUM(COALESCE(Jan, 0)) AS Jan,
    SUM(COALESCE(Feb, 0)) AS Feb,
    SUM(COALESCE(Mar, 0)) AS Mar,
    SUM(COALESCE(Apr, 0)) AS Apr
    -- 后续月份字段依此类推
  FROM your_table_name
  GROUP BY `Agent ID`, `Agent Name`;
END //
DELIMITER ;

注意事项 #

• 替换your_table_name为实际表名，补充完整所有月份字段；
• 若同一Agent ID对应多个不同Work Record ID，需根据业务规则调整聚合逻辑（如取最新记录、拼接所有ID等）；
• 触发器方案需注意高并发场景下的锁冲突，定时任务需避开业务高峰执行。

内容的提问来源于stack exchange，提问作者Malone Labe