基于出库记录与120天交付周期填充min/max字段的SQL更新查询实现

核心统计逻辑 #

• 统计维度：按物料编码PN分组
• 时间过滤规则：仅保留出库日期ISSUED_DATE距离统计基准日120天以内的记录
• 统计指标：有效出库量QTY_ISSUED的总和
• 样例校验：符合要求的记录求和结果为 2+1+2+5+1=11，自动排除超出时间范围的6月出库记录

实现代码 #

假设你的库存表名为inventory，需要更新的字段为min_stock、max_stock，出库记录表名为issues，关联键为PN，以下是不同数据库的适配写法：

MySQL 写法 #

UPDATE inventory i
INNER JOIN (
    SELECT 
        PN,
        SUM(QTY_ISSUED) AS total_issued_120d
    FROM issues
    WHERE ISSUED_DATE >= DATE_SUB(CURDATE(), INTERVAL 120 DAY)
    GROUP BY PN
) t ON i.PN = t.PN
SET 
    i.min_stock = t.total_issued_120d,
    i.max_stock = t.total_issued_120d;

PostgreSQL 写法 #

UPDATE inventory i
SET 
    min_stock = t.total_issued_120d,
    max_stock = t.total_issued_120d
FROM (
    SELECT 
        PN,
        SUM(QTY_ISSUED) AS total_issued_120d
    FROM issues
    WHERE ISSUED_DATE >= CURRENT_DATE - INTERVAL '120 days'
    GROUP BY PN
) t
WHERE i.PN = t.PN;

SQL Server 写法 #

UPDATE i
SET 
    min_stock = t.total_issued_120d,
    max_stock = t.total_issued_120d
FROM inventory i
INNER JOIN (
    SELECT 
        PN,
        SUM(QTY_ISSUED) AS total_issued_120d
    FROM issues
    WHERE ISSUED_DATE >= DATEADD(DAY, -120, GETDATE())
    GROUP BY PN
) t ON i.PN = t.PN;

自定义调整说明 #

• 如果需要固定统计基准日而非使用当前日期，将CURDATE()/CURRENT_DATE/GETDATE()替换为指定日期即可，比如替换为'2020-06-01'即可匹配样例数据的统计结果
• 如果min和max字段需要设置不同的系数，在赋值时乘以对应系数即可，比如min_stock = t.total_issued_120d * 0.8

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