再帰的 CTE を使用して SQL 行の枯渇する値を処理する方法

SQL: 行の減分値の処理

SQL で複数行のデータを操作する場合、行のセットから値を段階的に減算することは、特に減算された値がソース値を使い果たす場合には困難になることがあります。この種の問題を解決する方法は次のとおりです:

2 つのテーブルがあるとします。

• Pooled_Lots: 在庫情報 (ID、プール、ロット、数量) が含まれます
• Pool_Consumption: ストレージ消費量 (Id、PoolId、QuantityConsumed)

次のルールを考慮して、Pooled_Lots テーブルから QuantityConsumed 値を減算した結果セットが必要です:

• 最後以外の行の場合、QuantityConsumed が quantity 以下の場合は、quantity から quantumconsumed を減算します。
• 複数の行の場合は、数量から数量消費を減算します。
• 最後の行までループします。
• 最後の行では、数量から残りの 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 中国語 Web サイトの他の関連記事を参照してください。