CTE不会更新所有选定的行

用户名

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

我有与库存有关的下表：

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

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

我有一个查询，该查询基于FIFO计算累计和并用完存货：

;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

现在，我有一个相同的ProductID，其中DN中的数量和DimComb不同，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）。

任何帮助，将不胜感激。

败血症的

我认为它不更新第二行（即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）。

这意味着你要update你的Lot表使用sumDNQty的，所以首先你应该写一个汇总查询计算sum(DNQty)，这样就保证了每个LotsProductID都只有一个对应的DNProductID（在我的例子。在您的例子，你应该group by所有的字段你有你的join，比使用所有领域join的汇总结果Lot表）

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。