如何使用递归 CTE 处理 SQL 行中的耗尽值？

SQL：处理行中递减值

在SQL中，处理多行数据时，逐步从一组行中减去值可能具有挑战性，尤其是在减去的值会耗尽源值的情况下。以下是解决此类问题的方法：

假设您有两个表：

• Pooled_Lots: 包含库存信息 (Id, Pool, Lot, Quantity)
• Pool_Consumption: 存储消耗量 (Id, PoolId, QuantityConsumed)

您需要一个结果集，从Pooled_Lots表中减去QuantityConsumed值，并考虑以下规则：

• 对于非最后一行，如果QuantityConsumed小于或等于Quantity，则从Quantity中减去QuantityConsumed。
• 对于多行，从Quantity中减去QuantityConsumed。
• 循环直到最后一行。
• 对于最后一行，从Quantity中减去剩余的QuantityConsumed。

为了实现这一点，可以使用递归公用表表达式 (CTE)：

WITH Amos AS (
    -- 从每个Pool的Lot 1开始。
    SELECT
        PL.Pool,
        PL.Lot,
        PL.Quantity,
        PC.QuantityConsumed,
        CASE
            WHEN PC.QuantityConsumed IS NULL THEN PL.Quantity
            WHEN PL.Quantity >= PC.QuantityConsumed THEN PL.Quantity - PC.QuantityConsumed
            WHEN PL.Quantity = PC.QuantityConsumed THEN 0
            WHEN PL.Quantity < PC.QuantityConsumed THEN 0  --处理消耗量大于库存量的情况
            ELSE PL.Quantity
        END AS RunningQuantity,
        CASE
            WHEN PC.QuantityConsumed IS NULL THEN PL.Quantity
            ELSE PC.QuantityConsumed
        END AS RemainingDemand,
        CASE
            WHEN PL.Quantity >= PC.QuantityConsumed THEN 0
            ELSE PC.QuantityConsumed - PL.Quantity
        END AS SurplusOrDeficit
    FROM
        Pooled_Lots PL
    LEFT JOIN
        Pool_Consumption PC ON PL.Pool = PC.PoolId AND PL.Lot = 1  -- 关联到Lot 1
    UNION ALL
    SELECT
        a.Pool,
        a.Lot + 1,
        PL.Quantity,
        PC.QuantityConsumed,
        CASE
            WHEN a.RunningQuantity >= PC.QuantityConsumed THEN a.RunningQuantity - PC.QuantityConsumed
            WHEN a.RunningQuantity < PC.QuantityConsumed THEN 0
            ELSE a.RunningQuantity
        END AS RunningQuantity,
        CASE
            WHEN PC.QuantityConsumed IS NULL THEN 0
            ELSE PC.QuantityConsumed
        END AS RemainingDemand,
        CASE
            WHEN a.RunningQuantity >= PC.QuantityConsumed THEN 0
            ELSE PC.QuantityConsumed - a.RunningQuantity
        END AS SurplusOrDeficit
    FROM
        Amos a
    INNER JOIN
        Pooled_Lots PL ON a.Pool = PL.Pool AND a.Lot + 1 = PL.Lot
    LEFT JOIN
        Pool_Consumption PC ON PL.Pool = PC.PoolId AND PL.Lot = a.Lot + 1
)
SELECT * FROM Amos;

此CTE迭代地从Pooled_Lots行中减去QuantityConsumed值，直到到达最后一行。它根据指定的规则计算RunningQuantity、RemainingDemand和SurplusOrDeficit。

以上是如何使用递归 CTE 处理 SQL 行中的耗尽值？的详细内容。更多信息请关注PHP中文网其他相关文章！