现在有一组 条件 和 一个 数字(比如是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循环差值
根据条件可以查出若干条数据 ,比如是这么样子的
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循环差值
ID Num NumOut
1 4 0
2 5 0
3 8 0
4 6 0……你的更新数据ID Num NumOut
1 4 4
2 5 5
3 8 1
4 6 0……
按你的这个例子来说,到底Num减的是多少?糊涂了
表达的不清楚,就是有一个数A,
如果第一条记录不够减,Num-A(即4-10)< 0,相当于全部减去,NumOut为4
然后用第二条记录减去减去剩余差值,即 5-6,还是不够,
第三条8-1>0 ,到这里就可以了,NumOut为1,我也说不太清了
ID Num NumOut
1 4 4
2 5 5
3 8 7
4 6 5也不是你说的
ID Num NumOut
1 4 4
2 5 5
3 8 1
4 6 0……
--不知道以下是不是你想要的結果:
--創建表
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-10<0 THEN a.Num ELSE b.lj-10 END
FROM #temp a
INNER JOIN
(
SELECT id,(SELECT SUM(Num) FROM #temp WHERE id<=a.id) lj
FROM #temp a
) b ON a.id=b.id--查詢表
SELECT * FROM #temp
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 行受影响)
insert into #t (id,num,numout) values (1,4,0)
insert into #t (id,num,numout) values (2,5,0)
insert into #t (id,num,numout) values (3,8,0)
insert into #t (id,num,numout) values (4,6,0)
select * from #tdeclare @num int
declare @j int
declare @k int
declare @id int
set @num = 10
set @j = 0
set @k = 0DECLARE Num_cursor CURSOR FOR
SELECT id,num FROM #tOPEN Num_cursorFETCH NEXT FROM Num_cursor
INTO @id,@jWHILE @@FETCH_STATUS = 0
BEGIN
if (@k<@num)
begin
update #t set numout = case when (@k<@num) then @j else @num-@k+@j end where id = @id
end
set @k = @k+@j FETCH NEXT FROM Num_cursor
INTO @id,@j
ENDCLOSE Num_cursor
DEALLOCATE Num_cursor
GOselect * from #tdrop table #t
--结果
Id Num NumOut
----------- ----------- -----------
1 4 4
2 5 5
3 8 8
4 6 0
(4 行受影响)
insert into #t (id,num,numout) values (1,4,0)
insert into #t (id,num,numout) values (2,5,0)
insert into #t (id,num,numout) values (3,8,0)
insert into #t (id,num,numout) values (4,6,0)
select * from #tdeclare @num int
declare @j int
declare @k int
declare @id int
set @num = 10
set @j = 0
set @k = 0DECLARE Num_cursor CURSOR FOR
SELECT id,num FROM #tOPEN Num_cursorFETCH NEXT FROM Num_cursor
INTO @id,@jWHILE @@FETCH_STATUS = 0
BEGIN
if (@k<@num)
begin
update #t set numout = @j where id = @id
end
set @k = @k+@j
if (@k>@num)
begin
update #t set numout = case when (@num+@j-@k >0) then @num+@j-@k else 0 end where id=@id
end
FETCH NEXT FROM Num_cursor
INTO @id,@j
ENDCLOSE Num_cursor
DEALLOCATE Num_cursor
GOselect * from #tdrop table #t--结果
Id Num NumOut
----------- ----------- -----------
1 4 4
2 5 5
3 8 1
4 6 0
--終於明白你的要求了,再試試以下:
--創建表
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 tbcreate 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 intset @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
/*
ID Num NumOut
1 4 4
2 5 5
3 8 1
4 6 0
*/