How to Handle Depleting Values in SQL Rows Using Recursive CTEs?

SQL: Handling decrementing values ​​in rows

In SQL, when working with multiple rows of data, incrementally subtracting values ​​from a set of rows can be challenging, especially if the subtracted values ​​exhaust the source value. Here's how to fix this type of problem:

Suppose you have two tables:

• Pooled_Lots: Contains inventory information (Id, Pool, Lot, Quantity)
• Pool_Consumption: Storage consumption (Id, PoolId, QuantityConsumed)

You need a result set, subtracting the QuantityConsumed values ​​from the Pooled_Lots table, taking into account the following rules:

• For non-last rows, if QuantityConsumed is less than or equal to Quantity, subtract QuantityConsumed from Quantity.
• For multiple rows, subtract QuantityConsumed from Quantity.
• Loop until the last row.
• For the last row, subtract the remaining QuantityConsumed from the Quantity.

To achieve this, a recursive common table expression (CTE) can be used:

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;

This CTE iteratively subtracts the QuantityConsumed value from the Pooled_Lots rows until it reaches the last row. It calculates RunningQuantity, RemainingDemand and SurplusOrDeficit according to the specified rules.

The above is the detailed content of How to Handle Depleting Values in SQL Rows Using Recursive CTEs?. For more information, please follow other related articles on the PHP Chinese website!