mysql 字段a减字段b_SQL 数据库 如何实现第一行字段A减字段B得到值C，然后再用C减去第二行字段B，以此类推，求高手解答...

select * ,0 as 缺货 into tmp_r from table_1 --创建结果表

declare @i int

declare @j int

declare @q int

select row_number() over(order by 子件S) as id,子件S,现存量 into #tmp_s from table_1 group by 子件S,现存量 --第一个临时表将子件S列出来

set @i =1

while (@i<=(select max(id) from #tmp_s)) ---第一层循环，

begin

set @q = (select 现存量 from #tmp_s where id = @i)

select row_number() over(order by 子件S) as id,* ,0 as 缺货 into #tmp_i from table_1 where 子件S = (select 子件S from #tmp_s where id = @i) --第二个临时表，根据第一个临时表，逐个将子件S提取

set @j = 1

while (@j<=(select max(id) from #tmp_i)) --第二层循环，逐个子件算缺货

begin

set @q = @q - (select 消耗量 from #tmp_i where id = @j)

update #tmp_i set 缺货 = @q where id = @j

set @j= @j+1

if @q < 0 begin set @q=0 end --如果缺货小于0 则等于0

end

--将算好的一个子件更新到结果表

update tmp_r set 缺货 = #tmp_i.缺货 from #tmp_i where tmp_r.[生产订单号] = #tmp_i.[生产订单号] and tmp_r.子件S = #tmp_i.子件S

drop table #tmp_i --删除临时表

set @i = @i+1

end

drop table #tmp_s --删除临时表

select * from tmp_r