create table t (id int,sl int,ph char(3)) insert t select 1, 100 , '111' union all select 1 , 200 , '222' union all select 1 , 300 , '333' union all select 1 , 400, '444'alter procedure test @a int as declare @sl int,@ph char(3) declare cur cursor for select sl,ph from t open cur fetch next from cur into @sl,@ph while @@fetch_status = 0 and @a > 0 begin if @sl >= @a begin update t set sl = sl - @a where ph = @ph set @a = 0 end else begin update t set sl = 0 where ph = @ph set @a = @a - @sl end fetch next from cur into @sl,@ph end close cur deallocate curexec test 350 select * from t id sl ph ----------- ----------- ---- 1 0 111 1 0 222 1 250 333 1 400 444(所影响的行数为 4 行)
create table t (id int,sl int,ph char(3)) insert t select 1, 100 , '111' union all select 1 , 200 , '222' union all select 1 , 300 , '333' union all select 1 , 400, '444' go create procedure test @a int as declare @sl int,@ph char(3) declare cur cursor for select sl,ph from t open cur fetch next from cur into @sl,@ph while @@fetch_status = 0 and @a > 0 begin if @sl >= @a begin update t set sl = sl - @a where ph = @ph set @a = 0 end else begin update t set sl = 0 where ph = @ph set @a = @a - @sl end fetch next from cur into @sl,@ph end close cur deallocate cur go exec test 200 select * from t id sl ph ----------- ----------- ---- 1 0 111 1 100 222 1 300 333 1 400 444(所影响的行数为 4 行)
create procedure test @a int as update yourtable set sl=0 where sl=@a select * from yourtable order by sl go
没看清题意,上面的不对。 create procedure test @a int as declare @sl int,@ph char(3) declare cur cursor for select sl,ph from t order by sl open cur fetch next from cur into @sl,@ph while @@fetch_status = 0 and @a > 0 begin if @sl >= @a begin update t set sl = sl - @a where ph = @ph set @a = 0 end else begin update t set sl = 0 where ph = @ph set @a = @a - @sl end fetch next from cur into @sl,@ph end close cur deallocate curselect * from t order by sl go
--测试:create table 你的表(id int,sl int,ph int) insert 你的表 values(1, 100 , 111) insert 你的表 values(1 , 200 , 222) insert 你的表 values(1 , 300 , 333) insert 你的表 values(1 , 400 , 444)declare @你的参数 int set @你的参数=200 declare @i int update 你的表 set @i=case when @你的参数>sl then 0 else sl-@你的参数 end,@你的参数=case when @你的参数>sl then @你的参数-sl else 0 end,sl=@i select * from 你的表 go drop table 你的表id sl ph ----------- ----------- ----------- 1 0 111 1 100 222 1 300 333 1 400 444(所影响的行数为 4 行)
declare @你的参数 int set @你的参数=200 declare @i int update 你的表 set @i=case when @你的参数>sl then 0 else sl-@你的参数 end,@你的参数=case when @你的参数>sl then @你的参数-sl else 0 end,sl=@i一句话就可以了!!!楼主太心急了。
insert t select 1, 100 , '111'
union all select 1 , 200 , '222'
union all select 1 , 300 , '333'
union all select 1 , 400, '444'alter procedure test @a int
as
declare @sl int,@ph char(3)
declare cur cursor for select sl,ph from t
open cur
fetch next from cur into @sl,@ph
while @@fetch_status = 0 and @a > 0
begin
if @sl >= @a
begin
update t set sl = sl - @a where ph = @ph
set @a = 0
end
else
begin
update t set sl = 0 where ph = @ph
set @a = @a - @sl
end
fetch next from cur into @sl,@ph
end
close cur
deallocate curexec test 350
select * from t
id sl ph
----------- ----------- ----
1 0 111
1 0 222
1 250 333
1 400 444(所影响的行数为 4 行)
insert t select 1, 100 , '111'
union all select 1 , 200 , '222'
union all select 1 , 300 , '333'
union all select 1 , 400, '444'
go
create procedure test @a int
as
declare @sl int,@ph char(3)
declare cur cursor for select sl,ph from t
open cur
fetch next from cur into @sl,@ph
while @@fetch_status = 0 and @a > 0
begin
if @sl >= @a
begin
update t set sl = sl - @a where ph = @ph
set @a = 0
end
else
begin
update t set sl = 0 where ph = @ph
set @a = @a - @sl
end
fetch next from cur into @sl,@ph
end
close cur
deallocate cur
go
exec test 200
select * from t
id sl ph
----------- ----------- ----
1 0 111
1 100 222
1 300 333
1 400 444(所影响的行数为 4 行)
as
update yourtable set sl=0 where sl=@a
select * from yourtable order by sl
go
create procedure test @a int
as
declare @sl int,@ph char(3)
declare cur cursor for select sl,ph from t order by sl
open cur
fetch next from cur into @sl,@ph
while @@fetch_status = 0 and @a > 0
begin
if @sl >= @a
begin
update t set sl = sl - @a where ph = @ph
set @a = 0
end
else
begin
update t set sl = 0 where ph = @ph
set @a = @a - @sl
end
fetch next from cur into @sl,@ph
end
close cur
deallocate curselect * from t order by sl
go
insert 你的表 values(1, 100 , 111)
insert 你的表 values(1 , 200 , 222)
insert 你的表 values(1 , 300 , 333)
insert 你的表 values(1 , 400 , 444)declare @你的参数 int
set @你的参数=200
declare @i int
update 你的表 set @i=case when @你的参数>sl then 0 else sl-@你的参数 end,@你的参数=case when @你的参数>sl then @你的参数-sl else 0 end,sl=@i
select * from 你的表
go
drop table 你的表id sl ph
----------- ----------- -----------
1 0 111
1 100 222
1 300 333
1 400 444(所影响的行数为 4 行)
set @你的参数=200
declare @i int
update 你的表 set @i=case when @你的参数>sl then 0 else sl-@你的参数 end,@你的参数=case when @你的参数>sl then @你的参数-sl else 0 end,sl=@i一句话就可以了!!!楼主太心急了。