재귀 CTE를 사용하여 SQL 행의 값 고갈을 처리하는 방법은 무엇입니까?

SQL: 행에서 감소하는 값 처리

SQL에서는 여러 행의 데이터로 작업할 때 행 집합에서 값을 점진적으로 빼는 것이 어려울 수 있습니다. 특히 빼는 값이 소스 값을 모두 소모하는 경우 더욱 그렇습니다. 이러한 유형의 문제를 해결하는 방법은 다음과 같습니다.

두 개의 테이블이 있다고 가정해 보겠습니다.

• Pooled_Lots: 재고 정보 포함(ID, Pool, Lot, Quantity)
• Pool_Consumption: 스토리지 소비(Id, PoolId, QuantityConsumed)

다음 규칙을 고려하여 Pooled_Lots 테이블에서 QuantityConsumed 값을 뺀 결과 집합이 필요합니다.

• 마지막 행이 아닌 경우 QuantityConsumed가 Quantity보다 작거나 같으면 Quantity에서 QuantityConsumed를 뺍니다.
• 여러 행의 경우 수량에서 QuantityConsumed를 뺍니다.
• 마지막 행까지 반복합니다.
• 마지막 행의 경우 수량에서 남은 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!