基于收货交易的库存按库位账龄分配SQL实现技术问询

测试表结构与数据 #

创建表语句 #

-- 库存表：按物料+库位统计可用库存
CREATE TABLE STOCK (
    MAT_CODE VARCHAR(20),
    LOC_CODE VARCHAR(20),
    AVAIL_QTY DECIMAL(18,2),
    PRIMARY KEY (MAT_CODE, LOC_CODE)
);

-- 收货明细表：按日期降序存储收货记录
CREATE TABLE RECEIPT (
    MAT_CODE VARCHAR(20),
    TRANS_CODE VARCHAR(10),
    DOC_NO VARCHAR(30),
    RECEIPT_QTY DECIMAL(18,2),
    RECEIPT_DATE DATE,
    PRIMARY KEY (MAT_CODE, DOC_NO)
);

插入测试数据 #

-- 库存数据示例
INSERT INTO STOCK VALUES ('MAT001', 'LOC001', 150.00);
INSERT INTO STOCK VALUES ('MAT001', 'LOC002', 200.00);

-- 收货数据示例（按日期降序）
INSERT INTO RECEIPT VALUES ('MAT001', 'GR', 'DOC001', 100.00, '2024-05-20');
INSERT INTO RECEIPT VALUES ('MAT001', 'GR', 'DOC002', 150.00, '2024-05-15');
INSERT INTO RECEIPT VALUES ('MAT001', 'GR', 'DOC003', 200.00, '2024-05-10');

解决方案：基于窗口函数的纯SQL分配逻辑 #

核心思路是通过计算库存和收货的累计数量区间，用集合式查询实现匹配分配，替代易出错的游标逻辑。

WITH 
-- 1. 计算每个库位的库存累计区间
stock_cumulative AS (
    SELECT 
        MAT_CODE,
        LOC_CODE,
        AVAIL_QTY,
        SUM(AVAIL_QTY) OVER (PARTITION BY MAT_CODE ORDER BY LOC_CODE) AS CUMULATIVE_STOCK,
        SUM(AVAIL_QTY) OVER (PARTITION BY MAT_CODE ORDER BY LOC_CODE) - AVAIL_QTY AS PREV_CUMULATIVE_STOCK
    FROM STOCK
),
-- 2. 按收货日期降序计算收货累计区间
receipt_cumulative AS (
    SELECT 
        MAT_CODE,
        TRANS_CODE,
        DOC_NO,
        RECEIPT_QTY,
        RECEIPT_DATE,
        SUM(RECEIPT_QTY) OVER (PARTITION BY MAT_CODE ORDER BY RECEIPT_DATE DESC, DOC_NO) AS CUMULATIVE_RECEIPT,
        SUM(RECEIPT_QTY) OVER (PARTITION BY MAT_CODE ORDER BY RECEIPT_DATE DESC, DOC_NO) - RECEIPT_QTY AS PREV_CUMULATIVE_RECEIPT
    FROM RECEIPT
),
-- 3. 匹配区间并计算分配数量
allocation AS (
    SELECT 
        s.MAT_CODE,
        s.LOC_CODE,
        r.TRANS_CODE,
        r.DOC_NO,
        r.RECEIPT_DATE,
        -- 取区间重叠部分作为实际分配量
        CASE
            WHEN s.CUMULATIVE_STOCK <= r.PREV_CUMULATIVE_RECEIPT THEN 0
            WHEN s.PREV_CUMULATIVE_STOCK >= r.CUMULATIVE_RECEIPT THEN 0
            ELSE LEAST(s.CUMULATIVE_STOCK, r.CUMULATIVE_RECEIPT) - GREATEST(s.PREV_CUMULATIVE_STOCK, r.PREV_CUMULATIVE_RECEIPT)
        END AS ALLOC_QTY
    FROM stock_cumulative s
    JOIN receipt_cumulative r ON s.MAT_CODE = r.MAT_CODE
    HAVING ALLOC_QTY > 0
)
SELECT * FROM allocation ORDER BY MAT_CODE, LOC_CODE, RECEIPT_DATE DESC;

方案说明 #

1. 库存区间计算：按物料分组，对库位排序后生成累计库存值，确定每个库位库存对应的数量区间（比如LOC001对应0-150，LOC002对应150-350）。
2. 收货区间计算：按物料分组，按收货日期降序（最新收货优先）生成累计收货值，确定每个收货记录的供给区间（比如DOC001对应0-100，DOC002对应100-250）。
3. 区间匹配分配：通过比较库存与收货的区间重叠部分，精准计算每个库位在对应收货记录上的分配数量，确保所有库存和收货量完全匹配。

游标方案的问题 #

游标需要逐行处理数据，手动维护剩余数量，极易出现边界错误（比如剩余量计算偏差、循环终止条件错误），且数据量较大时性能远低于集合式查询。

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