如何用T-SQL每日批量扣减商品表库存（基于销售表数据）

表结构说明 #

先统一规范表列名（原表列名含空格，SQL中需用下划线或方括号处理）：

• 销售表（sales）：customer_id、itemforsaleID、quantity、price、total，新增IsProcessed标记列用于避免重复处理
• 商品表（goods）：ItemforsaleID、Product_name、quantity_left

1. 准备工作：给销售表添加处理标记列 #

如果销售表没有区分已处理/未处理记录的字段，先添加标记列防止每日重复扣减：

ALTER TABLE sales 
ADD IsProcessed BIT DEFAULT 0;

2. 核心扣减脚本（带事务保证一致性） #

脚本会统计当日未处理的销售总量，关联商品表扣减库存，之后标记销售记录为已处理，同时用事务确保操作原子性：

BEGIN TRANSACTION;

BEGIN TRY
    -- 按商品ID汇总未处理的销售总量，更新对应商品库存
    UPDATE g
    SET g.quantity_left = CASE 
        -- 可选：避免库存变为负数，根据业务需求调整逻辑
        WHEN g.quantity_left >= s.total_sold THEN g.quantity_left - s.total_sold 
        ELSE 0 
    END
    FROM goods g
    INNER JOIN (
        SELECT itemforsaleID, SUM(quantity) AS total_sold
        FROM sales
        WHERE IsProcessed = 0
        GROUP BY itemforsaleID
    ) s ON g.ItemforsaleID = s.itemforsaleID;

    -- 标记所有已处理的销售记录
    UPDATE sales
    SET IsProcessed = 1
    WHERE IsProcessed = 0;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- 抛出错误，也可添加自定义日志记录逻辑
    THROW;
END CATCH;

3. 配置每日自动执行 #

使用SQL Server代理创建定时作业：

• 新建作业，添加一个执行T-SQL脚本的步骤，将上述核心脚本粘贴进去
• 设置作业计划为每日指定时间执行（比如凌晨业务低峰期）

关键注意事项 #

• 库存负数控制：脚本中加入了CASE判断避免库存变为负数，若业务允许负库存可移除该判断
• 幂等性保证：通过IsProcessed标记确保每条销售记录只被处理一次，即使作业重复执行也不会重复扣减
• 错误处理：事务+TRY/CATCH块确保更新库存和标记记录的操作要么全成功，要么全回滚，避免数据不一致

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