Comment réduire la quantité de lots regroupés à l'aide du CTE récursif SQL ?

Utilisation du CTE récursif SQL pour décrémenter les quantités de lots groupés

Cette solution SQL Server 2005 utilise une expression de table commune (CTE) récursive pour réduire de manière itérative les quantités de lots regroupées en fonction des données de consommation. Le processus gère efficacement les scénarios dans lesquels la consommation dépasse la quantité disponible sur plusieurs lots d'un pool.

Voici le code SQL :

<code class="language-sql">-- Sample data setup
DECLARE @Pooled_Lots AS TABLE (
  Id INT,
  Pool INT,
  Lot INT,
  Quantity INT
);

INSERT INTO @Pooled_Lots (Id, Pool, Lot, Quantity) VALUES
(1, 1, 1, 5),
(2, 1, 2, 10),
(3, 1, 3, 4),
(4, 2, 1, 7),
(5, 3, 1, 1),
(6, 3, 2, 5);

DECLARE @Pool_Consumption AS TABLE (
  Id INT,
  PoolId INT,
  QuantityConsumed INT
);

INSERT INTO @Pool_Consumption (Id, PoolId, QuantityConsumed) VALUES
(1, 1, 17),
(2, 2, 8),
(3, 3, 10);


-- Recursive CTE to calculate remaining quantities
WITH LotQuantities AS (
    SELECT 
        PL.Pool, 
        PL.Lot, 
        PL.Quantity, 
        PC.QuantityConsumed,
        PL.Quantity as RunningQuantity,
        PC.QuantityConsumed 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
    WHERE PL.Lot = 1 -- Start with the first lot in each pool

    UNION ALL

    SELECT 
        lq.Pool, 
        PL.Lot, 
        PL.Quantity, 
        lq.QuantityConsumed,
        CASE WHEN lq.RemainingDemand > PL.Quantity THEN lq.RunningQuantity + PL.Quantity ELSE lq.QuantityConsumed END,
        CASE WHEN lq.RemainingDemand > PL.Quantity THEN lq.RemainingDemand - PL.Quantity ELSE 0 END,
        CASE WHEN lq.RemainingDemand > PL.Quantity THEN 0 ELSE lq.RemainingDemand - (lq.RunningQuantity + PL.Quantity) END
    FROM LotQuantities lq
    JOIN @Pooled_Lots PL ON lq.Pool = PL.Pool AND PL.Lot = lq.Lot + 1
    WHERE lq.RemainingDemand > 0
)
SELECT * FROM LotQuantities;</code>

L'ensemble de données résultant montre la quantité restante pour chaque lot après la déduction de la consommation. Le CTE traite de manière récursive les lots de chaque pool jusqu'à ce que la quantité consommée soit entièrement prise en compte. Notez que les valeurs négatives dans SurplusOrDeficit indiquent un déficit.

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!