MS Access如何实现优先扣减主库存 为0后依次扣减替代库存

以下提供两种可直接落地的实现方案，分别对应你提到的两种表结构设计，执行后均能得到你给出的预期扣减结果。

方案一：保留原有长表结构，新增色系优先级配置 #

该方案不需要改动你现有的InventoryCups和PrintList表结构，仅需新增一张配置表定义同色系的扣减顺序，灵活度高，后续新增替代色、调整扣减优先级仅需修改配置数据，不需要调整表结构和核心扣减逻辑。

• 第一步：新建色系扣减优先级配置表

CREATE TABLE ColorPriority (
    BaseColor VARCHAR(50) NOT NULL, -- 对应PrintList中待打印的主色名称
    SubColor VARCHAR(50) NOT NULL,  -- 实际参与扣减的库存颜色名称
    Priority INT NOT NULL,          -- 扣减优先级，数字越小越优先扣减，主色优先级固定为1
    PRIMARY KEY (BaseColor, SubColor)
);

-- 写入示例优先级配置
INSERT INTO ColorPriority VALUES
('True Blue', 'True Blue', 1),
('True Blue', 'Royal Blue', 2),
('True Blue', 'Navy Blue', 3),
('True Red', 'True Red', 1),
('True Red', 'Cherry Red', 2),
('True Red', 'Dark Red', 3);

• 第二步：执行分级扣减逻辑（以SQL Server语法为例，MySQL、PostgreSQL等数据库仅需微调窗口函数语法即可通用）

BEGIN TRANSACTION; -- 开事务保证数据一致性，避免中途出错导致库存错乱

-- 计算每个颜色实际需要扣减的数量
WITH StockWithPriority AS (
    SELECT 
        cp.SubColor,
        ic.QtyCurrent,
        pl.PrintQty AS TotalNeed,
        -- 计算比当前颜色优先级高的所有库存总和
        SUM(ic.QtyCurrent) OVER (
            PARTITION BY cp.BaseColor 
            ORDER BY cp.Priority 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS PrevStockSum,
        -- 计算包含当前颜色在内的累计库存总和
        SUM(ic.QtyCurrent) OVER (
            PARTITION BY cp.BaseColor 
            ORDER BY cp.Priority 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CumStockSum
    FROM ColorPriority cp
    INNER JOIN InventoryCups ic ON cp.SubColor = ic.Description
    INNER JOIN PrintList pl ON cp.BaseColor = pl.ToPrint
)
SELECT 
    SubColor AS Description,
    CASE
        WHEN ISNULL(PrevStockSum,0) >= TotalNeed THEN 0 -- 前面优先级的库存已够扣，当前颜色不扣
        WHEN CumStockSum >= TotalNeed THEN TotalNeed - ISNULL(PrevStockSum,0) -- 扣到当前颜色刚好够量，只扣差额
        ELSE QtyCurrent -- 累计库存仍不够，扣完当前颜色所有库存
    END AS DeductQty
INTO #TempDeductRecord
FROM StockWithPriority;

-- 校验是否存在库存不足的情况，可选
IF EXISTS (
    SELECT 1 
    FROM #TempDeductRecord tdr
    INNER JOIN InventoryCups ic ON tdr.Description = ic.Description
    WHERE tdr.DeductQty > ic.QtyCurrent
)
BEGIN
    RAISERROR('同色系库存不足，无法完成扣减',16,1);
    ROLLBACK TRANSACTION;
    DROP TABLE #TempDeductRecord;
    RETURN;
END

-- 更新实际库存
UPDATE ic
SET ic.QtyCurrent = ic.QtyCurrent - tdr.DeductQty
FROM InventoryCups ic
INNER JOIN #TempDeductRecord tdr ON ic.Description = tdr.Description;

-- 清理临时表，提交事务
DROP TABLE #TempDeductRecord;
COMMIT TRANSACTION;

执行以上逻辑后，库存结果和你给出的预期完全一致：主色先扣到0，剩余用量按优先级依次扣替代色。

方案二：调整为宽表结构（同色系多列存储） #

如果确定后续不会增加更多级别的替代色，可以采用你提到的宽表结构，把主色、一级替代、二级替代放在同一条记录的不同列，扣减逻辑更简单直白。

• 第一步：调整库存表结构

CREATE TABLE InventoryCups_Wide (
    ColorGroup VARCHAR(50) NOT NULL PRIMARY KEY, -- 色系名称，对应PrintList的ToPrint字段
    QtyMain INT NOT NULL DEFAULT 0,              -- 主色库存
    QtySub1 INT NOT NULL DEFAULT 0,              -- 一级替代色库存
    QtySub2 INT NOT NULL DEFAULT 0               -- 二级替代色库存
);

-- 写入示例库存数据
INSERT INTO InventoryCups_Wide VALUES
('True Blue', 20, 15, 5),
('True Red', 10, 15, 5);

• 第二步：执行分级扣减逻辑

BEGIN TRANSACTION;

-- 扣减前先校验总库存是否充足，可选
IF EXISTS (
    SELECT 1 
    FROM InventoryCups_Wide icw
    INNER JOIN PrintList pl ON icw.ColorGroup = pl.ToPrint
    WHERE icw.QtyMain + icw.QtySub1 + icw.QtySub2 < pl.PrintQty
)
BEGIN
    RAISERROR('同色系库存不足，无法完成扣减',16,1);
    ROLLBACK TRANSACTION;
    RETURN;
END

-- 按级别依次计算扣减后库存
UPDATE icw
SET 
    QtyMain = CASE WHEN QtyMain >= pl.PrintQty THEN QtyMain - pl.PrintQty ELSE 0 END,
    QtySub1 = CASE 
        WHEN QtyMain >= pl.PrintQty THEN QtySub1
        WHEN QtySub1 >= (pl.PrintQty - QtyMain) THEN QtySub1 - (pl.PrintQty - QtyMain)
        ELSE 0
    END,
    QtySub2 = CASE
        WHEN QtyMain + QtySub1 >= pl.PrintQty THEN QtySub2
        ELSE QtySub2 - (pl.PrintQty - QtyMain - QtySub1)
    END
FROM InventoryCups_Wide icw
INNER JOIN PrintList pl ON icw.ColorGroup = pl.ToPrint;

COMMIT TRANSACTION;

该方案的缺点是灵活度低，如果后续要新增三级、四级替代色，需要修改表结构新增字段，适合替代色级数固定的场景。

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