请教怎么自动分配数量

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 [email protected]>0 then qtyi else 0 end ,   @int=case when [email protected] >0 then 0 else @INT-qtyi end   where @INT>0select * from #TEMPDROP TABLE #TEMP

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>[email protected]  then @[email protected] 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 @[email protected], @[email protected], ID=case when @INT>0 then QTYI when @a>0 and @INT<0 then [email protected] else 0 end select * from #TEMPDROP TABLE #TEMP