Wie gehe ich mit der Erschöpfung von Werten in SQL-Zeilen mithilfe rekursiver CTEs um?

SQL: Umgang mit dekrementierenden Werten in Zeilen

Wenn in SQL mit mehreren Datenzeilen gearbeitet wird, kann das schrittweise Subtrahieren von Werten aus einer Reihe von Zeilen eine Herausforderung darstellen, insbesondere wenn die subtrahierten Werte den Quellwert erschöpfen. So beheben Sie ein solches Problem:

Angenommen, Sie haben zwei Tabellen:

• Pooled_Lots: Enthält Bestandsinformationen (ID, Pool, Los, Menge)
• Pool_Consumption:Speicherverbrauch (Id, PoolId, QuantityConsumed)

Sie benötigen einen Ergebnissatz, der die QuantityConsumed-Werte von der Tabelle Pooled_Lots subtrahiert und dabei die folgenden Regeln berücksichtigt:

• Wenn QuantityConsumed für nicht letzte Zeilen kleiner oder gleich Quantity ist, subtrahieren Sie QuantityConsumed von Quantity.
• Subtrahieren Sie bei mehreren Zeilen QuantityConsumed von Quantity.
• Schleife bis zur letzten Reihe.
• Subtrahieren Sie für die letzte Zeile die verbleibende QuantityConsumed von der Quantity.

Um dies zu erreichen, kann ein rekursiver gemeinsamer Tabellenausdruck (CTE) verwendet werden:

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;

Dieser CTE subtrahiert iterativ den QuantityConsumed-Wert von den Pooled_Lots-Zeilen, bis er die letzte Zeile erreicht. Es berechnet RunningQuantity, RemainingDemand und SurplusOrDeficit gemäß den angegebenen Regeln.

Das obige ist der detaillierte Inhalt vonWie gehe ich mit der Erschöpfung von Werten in SQL-Zeilen mithilfe rekursiver CTEs um?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!