SQL循环取差值，该怎么写！
现在有一组 条件 和 一个 数字（比如是10）
根据条件可以查出若干条数据 ，比如是这么样子的
ID Num NumOut
1 4 0
2 5 0
3 8 0
4 6 0……
按顺序 用每一条记录的 Num减去 数字，够减NumOut赋值为差值，不够减，NumOut赋值与Num相同，再用下一个减剩余数量
也就是说，以上的数据会被更新为：
ID Num NumOut
1 4 4
2 5 5
3 8 1
4 6 0……
这应该如何用sql语句表达？？？
按我的想法应该写个循环，判断的，但从来没写过sql的循环，查出的数据又该怎么保存呢？急用，多谢指教了！！！ sql? sql 循环 差值
[解决办法]
if object_id('Tempdb..#t') is not null drop table #t
create table #t(
[Id] int identity(1,1) not null,
[Num] int null,
[NumOut] int null
)
Insert Into #t
select 4,0 union all
select 5,0 union all
select 8,0 union all
select 6,0
declare @rowcount int--数据条数
declare @i int--计数器
declare @difference int--差
declare @a int--定义的数字
declare @tmp int--临时放定义的数字
set @a=10
set @tmp=@a
select @rowcount=count(1) from #t
set @i=1
while (@i<=@rowcount)
begin
select @difference=Num-@tmp from #t where id=@i
if(@difference>=0)
begin
update #t set NumOut=@difference where id=@i
set @tmp=@a
end
else
begin
update #t set NumOut=Num where id=@i
set @tmp=abs(@difference)
end
set @i=@i+1
end
select * from #t
-------------
--结果
Id Num NumOut
----------- ----------- -----------
1 4 4
2 5 5
3 8 7
4 6 6
(4 行受影响)
--終於明白你的要求了，再試試以下：
--創建表
create TABLE #temp (ID int,Num int,NumOut int)
INSERT #temp
SELECT 1,4,0 UNION ALL
SELECT 2,5,0 UNION ALL
SELECT 3,8,0 UNION ALL
SELECT 4,6,0
go
--更新表
UPDATE a SET NumOut=CASE WHEN b.lj<0 THEN 0 ELSE CASE WHEN b.lj>a.Num THEN a.Num ELSE b.lj END END
FROM #temp a
INNER JOIN
(
SELECT id,10-ISNULL((SELECT SUM(Num) FROM #temp WHERE id<a.id),0) lj
FROM #temp a
) b ON a.id=b.id
--查詢表
SELECT * FROM #temp
--drop table tb
create TABLE tb (ID int,Num int,NumOut int)
INSERT into tb
SELECT 1,4,0 UNION ALL
SELECT 2,5,0 UNION ALL
SELECT 3,8,0 UNION ALL
SELECT 4,6,0
go
--你要减的数
declare @num int
set @num = 10
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from tb
),
tt
as
(
select id,
num,
numout,
(select sum(num) from t t2
where t2.rownum <= t1.rownum) as sum_num
from t t1
)
--更新
update tt
set numout = case when sum_num <= @num
then num
when sum_num > @num and
@num - (sum_num - num) >=0
then @num - (sum_num - num)
else 0
end
--查询
select * from tb
/*
IDNumNumOut
144
255
381
460
*/
