sql - 使用来自其他表的动态值循环更新 SQL 表

Question

表 1:#tdac

insert #tdac(secnum,bucketcode,acc,defqty)
select 'ax1','cor',1,'012',-100
insert #tdac(secnum,bucketcode,acc,defqty)
select 'ax1','cor',2,'012',-50

表 2:#dac

insert #dac(secnum,bucketcode,acc,defqty)
select 'ax1','cor',0,'012',-125

我想更新表 1 中的 dfqty，方法是从表 2 中从最早的 caseid 开始减去 defqty。

If #tdac.defqty-#dac.defqty > 0, then 
    #tdac.defqty=0 and #dac.defqty = (#tdac.defqty-#dac.defqty) 
proceed to the next row. 
Continue this until the  value of (#tdac.defqty-#dac.defqty) becomes less than 0. 

在我的示例中，我想更新表 1，如下所示。请帮忙

secnum  bucketcode  caseid  acc     defqty
ax1     cor         1       012     0     ((-100 -(-125)=25)>0, So make it 0)
ax1     cor         2       012    -25    ((-50 -(-25)=-25)<0, So make it -25)

Answer 1

我不确定我是否完全理解您的逻辑，但我认为这可以通过递归 CTE 来完成:

我添加了另一个案例组来证明，这种方法可以一次性处理不同的组。

declare @tdac table(secnum varchar(100),bucketcode varchar(100),caseid int,acc varchar(100),defqty int);
insert @tdac(secnum,bucketcode,caseid,acc,defqty)
values('ax1','cor',1,'012',-100)
     ,('ax1','cor',2,'012',-50)
     ,('ax1','cor',3,'012',-150)
     ,('ax2','cor',1,'012',-100)
     ,('ax2','cor',2,'012',-100);

declare @dac table(secnum varchar(100),bucketcode varchar(100),caseid int,acc varchar(100),defqty int);
insert @dac(secnum,bucketcode,caseid,acc,defqty)
values('ax1','cor',0,'012',-125)
     ,('ax2','cor',0,'012',-150);

--第一个 CTE 将找到所有不同的组并将 @dac 的值作为起始值添加到集合中

WITH DistinctGroups AS
(
    SELECT t.secnum,t.bucketcode,t.acc,d.defqty
    FROM @tdac AS t
    LEFT JOIN @dac AS d ON d.secnum=t.secnum and d.bucketcode=t.bucketcode AND d.acc=t.acc
    GROUP BY t.secnum,t.bucketcode,t.acc,d.defqty
)

-递归 CTE

,recursiveCTE AS
(
    --anchor: Get the rows with the minimal caseid from each group

    SELECT t.secnum,t.bucketcode,t.caseid,t.acc,t.defqty,CASE WHEN x.NewQty>0 THEN 0 ELSE x.NewQty END AS NewQty,CASE WHEN x.NewQty>0 THEN x.NewQty ELSE 0 END AS NewDiff
    FROM @tdac AS t
    INNER JOIN DistinctGroups AS gr ON t.secnum=gr.secnum AND t.bucketcode=gr.bucketcode AND t.acc=gr.acc
    CROSS APPLY(SELECT t.defqty-gr.defqty AS NewQty) AS x
    WHERE t.caseid=(SELECT MIN(caseid) FROM @tdac AS t2 WHERE t2.secnum=gr.secnum AND t2.bucketcode=gr.bucketcode AND t2.acc=gr.acc)

    UNION ALL

    --find the row with the next caseid and add the diff value of the previous row

    SELECT t.secnum,t.bucketcode,t.caseid,t.acc,t.defqty,CASE WHEN x.NewQty>0 THEN 0 ELSE x.NewQty END AS NewQty,CASE WHEN x.NewQty>0 THEN x.NewQty ELSE 0 END AS NewDiff
    FROM @tdac AS t
    INNER JOIN recursiveCTE AS r ON t.secnum=r.secnum AND t.bucketcode=r.bucketcode AND t.acc=r.acc
    CROSS APPLY(SELECT t.defqty+r.NewDiff AS NewQty) AS x
    WHERE t.caseid=r.caseid+1
)
select * 
from recursiveCTE
order by secnum,caseid

结果

ax1 cor 1   012 -100       0    25
ax1 cor 2   012  -50     -25     0
ax1 cor 3   012 -150    -150     0

ax2 cor 1   012 -100       0    50
ax2 cor 2   012 -100     -50     0