请教怎么自动分配数量

请问如何自动分配数量啊

SQL code

CREATE TABLE #TEMP(D VARCHAR(20),QTYI INT,ID INT DEFAULT 0)insert #TEMP(D,QTYI)select 'A',1 union allselect 'A',4 union allselect 'A',3 union allselect 'A',3 union allselect 'A',5 union allselect 'A',2 union allselect 'A',3 UNION ALLSELECT 'A',5 DECLARE @INT INT =20UPDATE #TEMP SET ID=case when QTYI-@INT>0 then qtyi else 0 end ,   @int=case when QTYI-@INT >0 then 0 else @INT-qtyi end   where @INT>0select * from #TEMPDROP TABLE #TEMP

SQL code

CREATE TABLE #TEMP(D VARCHAR(20),QTYI INT,ID INT DEFAULT 0)insert #TEMP(D,QTYI)select 'A',1 union allselect 'A',4 union allselect 'A',3 union allselect 'A',3 union allselect 'A',5 union allselect 'A',2 union allselect 'A',3 UNION ALLSELECT 'A',5 DECLARE @INT INT ,@qty int,@n intselect @Int=20,@qty=0,@n=0UPDATE #TEMP SET  ID=case when @int>0 then case when @INT>=QTYI then qtyi else @INT end  else 0 end,@int=case when @INT>=@qty  then @INT-@qty else 0 end,@qty=qtyi  select * from #TEMP--DROP TABLE #TEMP/*D                    QTYI        ID          -------------------- ----------- ----------- A                    1           1A                    4           4A                    3           3A                    3           3A                    5           5A                    2           2A                    3           2A                    5           0（所影响的行数为 8 行）
[解决办法]
在定义一个变量

SQL code
CREATE TABLE #TEMP(D VARCHAR(20),QTYI INT,ID INT DEFAULT 0)insert #TEMP(D,QTYI)select 'A',1 union allselect 'A',4 union allselect 'A',3 union allselect 'A',3 union allselect 'A',5 union allselect 'A',2 union allselect 'A',3 UNION ALLSELECT 'A',5 DECLARE @INT INT =20,@a intUPDATE #TEMP SET @a=@INT, @INT=@INT-QTYI, ID=case when @INT>0 then QTYI when @a>0 and @INT<0 then qtyi+@INT else 0 end select * from #TEMPDROP TABLE #TEMP