如何在保持累积值的同时从多行中减去消耗值？

从多个批次中减去消耗价值

此问题涉及在多个库存批次 (Pooled_Lots) 之间分配消耗资源 (QuantityConsumed)，同时跟踪累计数量、剩余需求以及任何盈余或赤字。

数据结构：

我们有两张桌子：

• Pooled_Lots：包含每个库存批次的详细信息（池、批次、数量）。
• Pool_Conclusion：指定每个池消耗的总数量（PoolId、QuantityConsumed）。

期望的结果：

查询应该为Pooled_Lots中的每个批次生成一个结果集，包括：

• Pool、Lot、Quantity、QuantityConsumed、RunningQuantity、RemainingDemand、SurplusOrDeficit。

解决方法：

递归公用表表达式（CTE）提供了一种有效的解决方案。逻辑如下：

1. 使用每个池的第一批来初始化 CTE。
2. 对于每个池中的后续批次：
   • 如果批次的 RunningQuantity 超过剩余的 QuantityConsumed，则通过减去 Quantity 来更新 QuantityConsumed。
   • 将 RemainingDemand 计算为 QuantityConsumed 与 RunningQuantity 之和与当前批次 Quantity 之和之间的差值。
   • 对于每个池中的最后一批，计算 SurplusOrDeficit 作为 RunningQuantity 和 RemainingDemand 之间的差异。

SQL 查询（说明性）：

提供的查询不完整，并且包含一些逻辑不一致的地方。 强大的解决方案需要仔细处理边缘情况（例如，QuantityConsumed为零或超过池中所有批次的总数）。 需要基于特定的数据库系统（例如 SQL Server、PostgreSQL、MySQL）来设计更正且更高效的查询。 以下是更准确方法的概念概要：

<code class="language-sql">WITH RecursiveCTE AS (
    -- Anchor member: Select the first lot for each pool
    SELECT 
        PL.Pool, PL.Lot, PL.Quantity, PC.QuantityConsumed, 
        PL.Quantity AS RunningQuantity, 
        CASE WHEN PC.QuantityConsumed IS NULL THEN PL.Quantity ELSE PC.QuantityConsumed - PL.Quantity END AS RemainingDemand, 
        0 AS SurplusOrDeficit,
        ROW_NUMBER() OVER (PARTITION BY PL.Pool ORDER BY PL.Lot) as rn
    FROM Pooled_Lots PL
    LEFT JOIN Pool_Consumption PC ON PL.Pool = PC.PoolId
    WHERE PL.Lot = 1 --First lot

    UNION ALL

    -- Recursive member: Process subsequent lots
    SELECT 
        PL.Pool, PL.Lot, PL.Quantity, PC.QuantityConsumed,
        CASE 
            WHEN r.RemainingDemand > PL.Quantity THEN r.RunningQuantity - PL.Quantity
            ELSE r.RunningQuantity - r.RemainingDemand
        END AS RunningQuantity,
        CASE 
            WHEN r.RemainingDemand > PL.Quantity THEN r.RemainingDemand - PL.Quantity
            ELSE 0
        END AS RemainingDemand,
        CASE WHEN r.rn = (SELECT MAX(rn) FROM RecursiveCTE WHERE Pool = PL.Pool) THEN r.RunningQuantity - r.RemainingDemand ELSE 0 END AS SurplusOrDeficit,
        r.rn + 1
    FROM Pooled_Lots PL
    INNER JOIN RecursiveCTE r ON PL.Pool = r.Pool AND PL.Lot = r.rn + 1
    LEFT JOIN Pool_Consumption PC ON PL.Pool = PC.PoolId
)
SELECT * FROM RecursiveCTE
ORDER BY Pool, Lot;</code>

结果：

输出将显示每批的详细信息，包括累计RunningQuantity、剩余RemainingDemand以及分配后的任何SurplusOrDeficit。 RunningQuantity、RemainingDemand 和 SurplusOrDeficit 计算的准确性取决于递归 CTE 中实现的精确逻辑来处理所有可能的场景。 完整且经过测试的解决方案需要了解特定的数据库系统以及用于验证的潜在样本数据。

以上是如何在保持累积值的同时从多行中减去消耗值？的详细内容。更多信息请关注PHP中文网其他相关文章！