如何通过递归查询或循环查询计算物料库存耗尽日期？

实现思路 #

不用递归查询，靠窗口函数就能搞定核心逻辑：按物料分组、日期排序计算累计需求量，再和仓库库存对比，找出第一个累计需求超过库存的日期，就是该物料的库存耗尽日。

基础版SQL语句 #

适用于确定所有物料都会出现库存耗尽的场景：

WITH MaterialCumulativeDemand AS (
    SELECT 
        t2.Material,
        t2.Date,
        SUM(t2.quantity) OVER (PARTITION BY t2.Material ORDER BY t2.Date) AS CumulativeDemand,
        t1.quantity AS StockQuantity
    FROM Tab2 t2
    INNER JOIN Tab1 t1 ON t2.Material = t1.Material
)
SELECT 
    Material,
    MIN(Date) AS StockExhaustDate
FROM MaterialCumulativeDemand
WHERE CumulativeDemand > StockQuantity
GROUP BY Material;

代码说明 #

1. CTE部分：关联Tab1和Tab2，用SUM() OVER(PARTITION BY t2.Material ORDER BY t2.Date)计算每个物料从最早日期到当前日期的累计需求量
2. 主查询：筛选出累计需求超过库存的记录，按物料取最早的日期，就是库存耗尽的时间点

增强版SQL语句 #

如果存在部分物料库存永远充足的情况，这个版本会返回明确提示：

WITH MaterialCumulativeDemand AS (
    SELECT 
        t1.Material,
        t2.Date,
        SUM(t2.quantity) OVER (PARTITION BY t1.Material ORDER BY t2.Date) AS CumulativeDemand,
        t1.quantity AS StockQuantity
    FROM Tab1 t1
    LEFT JOIN Tab2 t2 ON t1.Material = t2.Material
),
ExhaustDateTemp AS (
    SELECT 
        Material,
        MIN(Date) AS StockExhaustDate
    FROM MaterialCumulativeDemand
    WHERE CumulativeDemand > StockQuantity
    GROUP BY Material
)
SELECT 
    t1.Material,
    COALESCE(e.StockExhaustDate, '库存未耗尽') AS StockExhaustDate
FROM Tab1 t1
LEFT JOIN ExhaustDateTemp e ON t1.Material = e.Material;

代码说明 #

1. 用LEFT JOIN关联Tab1和Tab2，确保所有物料都被包含
2. 用COALESCE函数处理库存未耗尽的物料，返回自定义提示文本

内容的提问来源于stack exchange，提问作者Jan Janoušek