合并三条SQL更新语句为单语句，创建员工薪资更新存储过程

合并后的单条UPDATE语句 #

通过预计算每个员工的奖金总额和最新奖金日期，一次性关联更新employee_1表，避免多次扫描表提升效率：

UPDATE employee_1 e
JOIN (
    SELECT 
        employee_id,
        SUM(bonus_amount) AS total_bonus,
        MAX(bonus_date) AS latest_bonus_date
    FROM bonus_1
    GROUP BY employee_id
) b ON e.employee_id = b.employee_id
SET 
    e.salary = e.old_salary + b.total_bonus,
    e.last_bonus_date = b.latest_bonus_date;

封装成存储过程 #

把上述语句做成存储过程，方便重复调用：

DELIMITER //

CREATE PROCEDURE UpdateEmployeeSalaryAndBonusDate()
BEGIN
    UPDATE employee_1 e
    JOIN (
        SELECT 
            employee_id,
            SUM(bonus_amount) AS total_bonus,
            MAX(bonus_date) AS latest_bonus_date
        FROM bonus_1
        GROUP BY employee_id
    ) b ON e.employee_id = b.employee_id
    SET 
        e.salary = e.old_salary + b.total_bonus,
        e.last_bonus_date = b.latest_bonus_date;
END //

DELIMITER ;

补充说明 #

• 用JOIN替代原语句里的多次子查询，减少数据库重复扫描，执行效率更高
• 直接将old_salary与总奖金相加赋值给salary，一步完成原第三条语句的薪资累加逻辑
• 如果要处理无奖金记录的员工（避免salary或last_bonus_date被置为NULL），可以改用LEFT JOIN配合COALESCE：

  UPDATE employee_1 e
  LEFT JOIN (
      SELECT 
          employee_id,
          SUM(bonus_amount) AS total_bonus,
          MAX(bonus_date) AS latest_bonus_date
      FROM bonus_1
      GROUP BY employee_id
  ) b ON e.employee_id = b.employee_id
  SET 
      e.salary = e.old_salary + COALESCE(b.total_bonus, 0), -- 无奖金则加0，薪资保持原old_salary
      e.last_bonus_date = COALESCE(b.latest_bonus_date, e.last_bonus_date); -- 无奖金则保留原日期
  

内容的提问来源于stack exchange，提问作者4PS18IS039 SAMARTH