Comment gérer l'épuisement des valeurs dans les lignes SQL à l'aide de CTE récursifs ?

SQL : Gestion des décrémentations de valeurs dans les lignes

En SQL, lorsque vous travaillez avec plusieurs lignes de données, soustraire progressivement des valeurs d'un ensemble de lignes peut être difficile, surtout si les valeurs soustraites épuisent la valeur source. Voici comment résoudre ce type de problème :

Supposons que vous ayez deux tables :

• Pooled_Lots : Contient des informations d'inventaire (Id, Pool, Lot, Quantité)
• Pool_Consumption : Consommation de stockage (Id, PoolId, QuantityConsumed)

Vous avez besoin d'un ensemble de résultats, en soustrayant les valeurs QuantityConsumed de la table Pooled_Lots, en tenant compte des règles suivantes :

• Pour les lignes autres que les dernières, si QuantityConsumed est inférieur ou égal à Quantity, soustrayez QuantityConsumed de Quantity.
• Pour plusieurs lignes, soustrayez QuantityConsumed de Quantity.
• Boucle jusqu'au dernier rang.
• Pour la dernière ligne, soustrayez la quantité restante de la quantité.

Pour y parvenir, une expression de table commune récursive (CTE) peut être utilisée :

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;

Ce CTE soustrait de manière itérative la valeur QuantityConsumed des lignes Pooled_Lots jusqu'à ce qu'elle atteigne la dernière ligne. Il calcule RunningQuantity, RemainingDemand et SurplusOrDeficit selon les règles spécifiées.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!