用一句UPDATE就搞定好象有困难,我是不行了,等高手吧...
我的方法如下:
create table tt (code varchar(3),pnum int,price numeric(2,1),onum int )
insert into tt values('001',50,5.5,0)
insert into tt values('001',60,4.2,0)
insert into tt values('001',40,4.3,0)
insert into tt values('001',100,3.2,0)
insert into tt values('001',200,3.1,0)
insert into tt values('001',5000,2.2,0)
declare @tmptt table(code varchar(3),pnum int,price numeric(2,1),onum int)
declare @mm int
insert into @tmptt select * from tt order by code,price desc
select @mm=300
update @tmptt set @mm=@mm-pnum,onum = case when @mm>=0 then pnum when @mm<0 and pnum+@mm >0 then pnum+@mm else 0 end where code='001'
delete from tt
insert into tt select * from @tmptt
select * from tt
我的方法如下:
create table tt (code varchar(3),pnum int,price numeric(2,1),onum int )
insert into tt values('001',50,5.5,0)
insert into tt values('001',60,4.2,0)
insert into tt values('001',40,4.3,0)
insert into tt values('001',100,3.2,0)
insert into tt values('001',200,3.1,0)
insert into tt values('001',5000,2.2,0)
declare @tmptt table(code varchar(3),pnum int,price numeric(2,1),onum int)
declare @mm int
insert into @tmptt select * from tt order by code,price desc
select @mm=300
update @tmptt set @mm=@mm-pnum,onum = case when @mm>=0 then pnum when @mm<0 and pnum+@mm >0 then pnum+@mm else 0 end where code='001'
delete from tt
insert into tt select * from @tmptt
select * from tt
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货