sql-server - CTE 未更新所有选定的行

Question

我正在尝试在库存上实现 FIFO。

我有以下与库存相关的表格：

Lot:
ID,SiteID,WHID,BatchID,Qty,Amount,QtyRemaning,AmountRemaning,LastQtyOut,DimComb, . . .

Dispatch Note:
ID,ProductID,SiteID,WHID,Quantity,DimComb, . . .

我有一个查询，它根据 FIFO 计算累积和并 OUT 库存：

;WITH CTE AS (
    SELECT  
        DN.fldQty as DNQty,
        DN.fldProductID as DNProductID,
        lot.fldProductID as LotsProductID,
        lot.fldID as LotID,
        lot.fldQtyRemaning as QtyRemaning,
        lot.fldUnitCost as UnitCost,
        lot.fldQty as LotQty,
        lot.fldAmountRemaning as AmountRemaning,
        lot.fldLastQtyOut as LastQtyOut,
        lot.fldLastUpdateRefDoc as fldLastUpdateRefDoc,
        lot.fldLastUpdateRefDocNum as fldLastUpdateRefDocNum,
        DN.DimComb as DNDimComb,
        lot.DimComb,
        CumulativeSum= SUM(lot.fldQtyRemaning) 
        OVER 
        (PARTITION BY 
        DN.DimComb,
        lot.fldProductID,
        lot.fldSiteID,
        lot.fldWHID, 
        lot.fldLocationID,
        lot.fldPalletID--,
        --lot.fldBatchID 
        ORDER BY lot.fldID  ROWS UNBOUNDED PRECEDING) 
    FROM 
        #tmpTblDN DN
    RIGHT JOIN
        lot lot
    ON
        --lot.fldRefDocNum              =   tblGRNItems.fldGRNID                AND 
        ISNULL(DN.fldProductID,0) = ISNULL(lot.fldProductID,0) AND                              
        ISNULL(DN.fldSiteID,0)      =   ISNULL(lot.fldSiteID,0)         AND
        ISNULL(DN.fldWHID,0)        =   ISNULL(lot.fldWHID,0)           AND
        ISNULL(DN.fldLocationID,0)  =   ISNULL(lot.fldLocationID,0)     AND
        ISNULL(DN.fldPalletID,0)    =   ISNULL(lot.fldPalletID,0)       --AND
        --ISNULL(tblSIRItems.fldBatchID,0)    =   ISNULL(lot.fldBatchID,0)
    WHERE 
            DN.fldDNID              =       @DNID   
    AND     lot.fldQtyRemaning              >       0
)

UPDATE 
    CTE
SET
    QtyRemaning = CASE 

            WHEN (CumulativeSum - DNQty) < 0 AND DNProductID = LotsProductID THEN  0
            WHEN (CumulativeSum - DNQty) BETWEEN 0 AND QtyRemaning AND DNProductID = LotsProductID THEN (CumulativeSum - DNQty)
            ELSE QtyRemaning 
           END ,
    LastQtyOut = CASE 
            WHEN (CumulativeSum - DNQty) < 0 AND DNProductID = LotsProductID THEN QtyRemaning 
            WHEN (CumulativeSum - DNQty) BETWEEN 0 AND QtyRemaning AND DNProductID = LotsProductID THEN 
                CASE WHEN LotQty = QtyRemaning THEN
                    (QtyRemaning - (CumulativeSum - DNQty))
                ELSE
                    (CumulativeSum - (CumulativeSum - DNQty))
                END

            ELSE LastQtyOut 
           END ,
    AmountRemaning = CASE 
            WHEN (CumulativeSum - DNQty) < 0 AND DNProductID = LotsProductID THEN AmountRemaning - (QtyRemaning * UnitCost)
            WHEN (CumulativeSum - DNQty) BETWEEN 0 AND QtyRemaning AND DNProductID = LotsProductID THEN 
                CASE WHEN LotQty = QtyRemaning THEN
                    AmountRemaning - ((QtyRemaning - (CumulativeSum - DNQty)) * UnitCost)
                ELSE
                    AmountRemaning - ((CumulativeSum - (CumulativeSum - DNQty)) * UnitCost)
                END
            ELSE AmountRemaning 
           END ,
    fldDimComb = DNDimComb,
    fldLastUpdateRefDoc = 'DispatchNote',
    fldLastUpdateRefDocNum = @DNID

FROM CTE
WHERE CumulativeSum <= QtyRemaning + DNQty

现在我遇到了这样的情况，即我在 DN 中具有不同数量和不同 DimComb 的相同 ProductID，CTE 在选择时显示以下结果：

DNQty | DNProductID | LotsProductID | LotID | QtyRemaning | UnitCost | LotQty | AmountRemaning | LastQtyOut | CumulativeSum |
2     | 14          | 14            | 783   | 100         | 3        | 100    | 300            | NULL       | 100           |
3     | 14          | 14            | 783   | 100         | 3        | 100    | 300            | NULL       | 100           |

但问题是，它只用 DNQty 2 的第一行更新 Lot 表。

我还评论了 WHERE 子句，但它也不起作用。我认为它没有用第二行（即具有 DNQty 3 的行）更新 Lot 表，因为这两行都引用 Lot 表中的同一行（即 LotID 783）。

任何帮助，将不胜感激。

标签： sql-servercommon-table-expressionfifo

Answer 1

我认为它没有用第二行（即具有 DNQty 3 的行）更新 Lot 表，因为这两行都引用 Lot 表中的同一行（即 LotID 783）。

是的，正是如此。

您的示例可以简化为：

create table #tmpTblDN (DNQty int, DNProductID int);
insert into #tmpTblDN values (2, 14), (3, 14);

create table #lot (LotsProductID int, 
                   LotID int, 
                   QtyRemaning int, 
                   UnitCost int, 
                   LotQty int, 
                   AmountRemaning int,
                   LastQtyOut int, 
                   CumulativeSum int);
insert into #lot values
(14, 783, 100, 3, 100, 300, NULL, 100)  


;WITH CTE AS 
(
    SELECT *
    FROM 
        #tmpTblDN DN
    RIGHT JOIN
        #lot lot
           on dn.DNProductID = lot.LotsProductID
)

UPDATE 
    CTE
SET
--select *,
    QtyRemaning = CASE 
            WHEN (CumulativeSum - DNQty) < 0 AND DNProductID = LotsProductID THEN  0
            WHEN (CumulativeSum - DNQty) BETWEEN 0 AND QtyRemaning AND DNProductID = LotsProductID THEN (CumulativeSum - DNQty)
            ELSE QtyRemaning 
           END 

FROM CTE
WHERE CumulativeSum <= QtyRemaning + DNQty

所以你清楚地看到你的第一个表包含 2 行，但是你做了一个update只有一行的第二个表（通过 cte）。

对此的解释可以在“根据其他表中的数据更新数据”部分下的BOL 文章UPDATE (Transact-SQL)中找到：

在这种情况下，我想通过减去 DNQty 2 和 3 来更新具有 ID 783 的 Lot（即在第一次更新中 LotQtyRemaning 的值应为 98，在第二次更新后它的值应为 95）。

这意味着您希望使用DNQty 来访问update您的表，因此首先您应该编写一个计算的聚合查询，这样您就可以保证每个都只有一个对应的（在我的示例中。在您的示例中，您应该将所有字段你有你的，而不是使用所有这些字段到你的聚合结果到表中）Lotsumsum(DNQty)LotsProductIDDNProductIDgroup byjoinjoinLot

update lot
set QtyRemaning = CASE 
                    WHEN (CumulativeSum - DNQty) < 0 AND DNProductID = LotsProductID THEN  0
                    WHEN (CumulativeSum - DNQty) BETWEEN 0 AND QtyRemaning AND DNProductID = LotsProductID THEN (CumulativeSum - DNQty)
                    ELSE QtyRemaning 
                   END 
from #lot lot
     join (select DNProductID, sum(DNQty) as DNQty
           from #tmpTblDN
           group by DNProductID) dn
             on dn.DNProductID = lot.LotsProductID