declare @i int
set @i=100表1:
流水ID col_1 col_2
1 30
2 20
3 40
--------------------
要求结果:
流水ID col_1 col_2
1 30 30
2 20 20
3 60 50
---------------
说明:把@i按行的先后次序进行分配列col_1,如上,当分配到第三行(即流水ID为3的那一行时)由于@i只剩下50,因此col_2只能为50而不能为60。要求:不用游标,直接用SQL语句实现。
请大家帮帮忙,火线放分!
set @i=100表1:
流水ID col_1 col_2
1 30
2 20
3 40
--------------------
要求结果:
流水ID col_1 col_2
1 30 30
2 20 20
3 60 50
---------------
说明:把@i按行的先后次序进行分配列col_1,如上,当分配到第三行(即流水ID为3的那一行时)由于@i只剩下50,因此col_2只能为50而不能为60。要求:不用游标,直接用SQL语句实现。
请大家帮帮忙,火线放分!
insert into #(col_1) select 30
insert into #(col_1) select 20
insert into #(col_1) select 60declare @i int
set @i=100 select a.id,a.col_1,col_2=(case when @i-(select sum(col_1) from # where id<=a.id)>0
then a.col_1
else @i-(select sum(col_1) from # where id<a.id) end)
from # aid col_1 col_2
----------- ----------- -----------
1 30 30
2 20 20
3 60 50(3 行受影响)
declare @t table(id int,col_1 int,col_2 int)
insert into @t select 1,30,0
insert into @t select 2,20,0
insert into @t select 3,60,0
insert into @t select 4,40,0declare @num int
set @num=100
update @t set col_2=(
case when a.id<c.id then a.col_1 else @num-(select sum(col_1) from @t where id<c.id) end)
from @t a,
(select top 1 * from
(select *,cnum=(select sum(col_1) from @t where id<=a.id) from @t a)b
where cnum>=@num
order by cnum)c
where a.id<=c.id
select * from @t
declare @t table(ID int ,col1 int,col2 int)
insert into @t select 1,30,null
union all select 2,20,null
union all select 3,60,null
select * from @t
declare @i int
set @i =100
declare @j int
declare @k int
set @k=1
while @i>0
begin
select @j=col1 from @t where ID=@k
if @i<@j
update @t set col2=@i where ID=@k
else
update @t set col2=@j where ID=@k
set @i=@i-@j
set @k=@k+1
end
select * from @t
set nocount off
--1楼的用如下数据测试会出错:declare @t table(id int,col_1 int,col_2 int)
insert into @t select 1,30,0
insert into @t select 2,20,0
insert into @t select 3,60,0
insert into @t select 4,40,0declare @i int
set @i=100 select a.id,a.col_1,col_2=(case when @i-(select sum(col_1) from # where id<=a.id)>0
then a.col_1
else @i-(select sum(col_1) from # where id<a.id) end)
from # a
--结果:
id col_1 col_2
1 30 30
2 20 20
3 60 50
4 40 -10
1 30
2 20
3 40
--------------------
要求结果:
流水ID col_1 col_2
1 30 30
2 20 20
3 60 50 第三条数据
是写错了?还是有特殊要求啊?
http://topic.csdn.net/u/20080516/13/46d38e03-81eb-407a-85e4-5d33dd013d4c.html,
我将对本贴在稍后进行友情放分,人人有份。请大家去我的新贴看看,http://topic.csdn.net/u/20080516/15/0e41b8ed-2866-49b9-a5c0-9f0bdf8dfbd4.html。谢谢!