如何使用遞歸 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)：

<code class="language-sql">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;</code>

此CTE迭代地從Pooled_Lots行中減去QuantityConsumed值，直到到達最後一行。它根據指定的規則計算RunningQuantity、RemainingDemand和SurplusOrDeficit。

以上是如何使用遞歸 CTE 處理 SQL 行中的耗盡值？的詳細內容。更多資訊請關注PHP中文網其他相關文章！