关于一个 for循环

[wangy2j]

在sql里的表里得到如下数据
字段 消费金额 余额
20 100
30 20
10 10
8 2
由于第二行数据出现错误，余额20本应该为80 应为只消费了20，少了50元
现在就要把20改为70，由于这个改变了，所以余下的数据都要改，下面的余额就要是60和52
如果后面还有数据错了 也要接着改
求代码

[47522341]

update tablename
set 余额=余额+50
where 余额<100

[47522341]

只是针对这个题目的话，应该不用进行循环的．

[wangy2j]

我只是举个简单的例子
实际错误的数据有几w条。。。。

[47522341]

将错误的具体情况说出来

[wangy2j]

就是机器出了问题 余额被多扣了 现在要把这个消费表补好
根据消费金额来计算正确的余额 现在所有卡号我都放在combobox里了
根据每个卡号的数据分别来计算

现在只求一个人的余额少了 怎么把他的几十条的余额数据改了

[wangy2j]

在代码里怎么写 可以找出上一条数据的 余额 来和下面的余额和消费金额的和做比较

[wangy2j]

来帮帮忙

[47522341]

用更新游标可行。
if exists(select 1 from sysobjects where id = object_id(N'mytest'))
and (objectproperty(object_id('mytest'),'IsTable') = 1)
drop table mytest

create table mytest
(
FID nvarchar(10), --卡号
FConsume float, --消费金额
Fspare float --剩余金额
)

insert into mytest values('a001',20,100)
insert into mytest values('a001',30,20)
insert into mytest values('a001',10,10)
insert into mytest values('a001',8,2)

select * from mytest

declare
@FID nvarchar(10),
@FConsume float,
@FSpare float,

@FID1 nvarchar(10),
@FSpare1 float

select @FID1 = '00000' --一个非正常值

declare mycursor cursor
for
select FID, FConsume,FSpare
from Mytest
order by FID
for update of FSpare

open mycursor

fetch mycursor
into @FID, @FConsume, @FSpare

while @@Fetch_status = 0
begin
if @FID1 = @FID
begin
set @FSpare1 = @FSpare1 - @FConsume
update mytest set FSPare = @FSpare1
where current of mycursor
end
else begin
set @FID1 = @FID
set @FSpare1 = @FSpare
end

fetch next from mycursor into @FID, @FConsume, @FSpare
end

close mycursor
deallocate mycursor


select * from mytest

[47522341]

不好意思
这里不能用orde by

[47522341]

if exists(select 1 from sysobjects where id = object_id(N'mytest'))
and (objectproperty(object_id('mytest'),'IsTable') = 1)
drop table mytest

create table mytest
(
FID nvarchar(10), --卡号
FConsume float, --消费金额
Fspare float --剩余金额
)

insert into mytest values('a001',20,100)
insert into mytest values('a001',30,20)
insert into mytest values('a001',10,10)
insert into mytest values('a001',8,2)

select * from mytest

declare
@FID nvarchar(10),
@FConsume float,
@FSpare float,

@FID1 nvarchar(10),
@FSpare1 float

select @FID1 = '00000' --一个非正常值

declare mycursor cursor
for
select FID, FConsume,FSpare
from Mytest
for update of FSpare

open mycursor

fetch mycursor
into @FID, @FConsume, @FSpare

while @@Fetch_status = 0
begin
if @FID1 = @FID
begin
set @FSpare1 = @FSpare1 - @FConsume
update mytest set FSPare = @FSpare1
where current of mycursor
end
else begin
set @FID1 = @FID
set @FSpare1 = @FSpare
end

fetch next from mycursor into @FID, @FConsume, @FSpare
end

close mycursor
deallocate mycursor


select * from mytest

[wangy2j]

procedure Tmain.SpeedButton1Click(Sender: TObject);
var
i:integer;
x,y:double;
t:tdatetime;
begin

with adoquery3 do //给y,t第一次赋值
begin
close;
sql.Clear;
sql.Add('select 卡片余额,消费金额,消费时间 from trade '+
'where 消费时间 in (select top 1(消费时间) from trade '+
'where 消费时间<:b and 卡号='''+combobox1.text+''' order by 消费时间 desc) and 卡号='''+combobox1.text+''' ');
parameters.ParamByName('b').Value := strtodatetime('2006-7-1 1:00:00') ;
open;
y:=FieldValues['卡片余额']+FieldValues['消费金额'];
t:=fieldvalues['消费时间'];
end;

with adoquery1 do //把数据装入adoquery1进行循环
begin
close;
sql.Clear;
sql.add('select 交易编号,卡号,人员姓名,消费金额,卡片余额,消费时间 '+
'from trade '+
'where 卡号='''+combobox1.text+''' and 消费时间>=(select top 1(消费时间) from trade '+
'where 消费时间<:b and 卡号='''+combobox1.text+''' order by 消费时间 desc) order by 消费时间');
Parameters.ParamByName('b').Value := strtodatetime('2006-7-1 1:00:00') ;
open;
end;
//for i:=0 to adoquery1.Fields.Count-1 do

adoquery1.First;
while not adoquery1.Eof do
begin
//adoquery4.active:=false;
adoquery4.Close; //将充值数据截取
adoquery4.SQL.Clear;
adoquery4.SQL.Add('select 购买金额 from buy where 购买日期>:e and 购买日期<:f and 卡号='''+combobox1.text+'''');
adoquery4.parameters.ParamByName('e').Value :=t;
adoquery4.parameters.parambyname('f').value :=adoquery1.fieldvalues['消费时间'];
adoquery4.open;
t:=adoquery1.fieldvalues['消费时间'];
//adoquery4.Active:=true;
if (adoquery4.fieldcount=1) and (adoquery4.FieldValues['购买金额']=null) then//两时间点没有充值
begin
adoquery1.Edit;
x:=adoquery1.fieldvalues['消费金额'];
adoquery1.fieldvalues['卡片余额']:=y-x;
y:=adoquery1.fieldvalues['卡片余额'];
adoquery1.Post;
end;

if (adoquery4.fieldcount=1) and (adoquery4.fieldvalues['购买金额']<>null)then //充值一次
begin
adoquery1.Edit;
x:=adoquery1.fieldvalues['消费金额'];
adoquery1.fieldvalues['卡片余额']:=y-x+adoquery4.FieldValues['购买金额'];
y:=adoquery1.fieldvalues['卡片余额'];
adoquery1.Post;
end;
if adoquery4.Fieldcount>1 then//充值多次
begin
showmessage('充值记录过多，请手动改动');
end;
adoquery1.Next;
end;


end;

前天解决了 加入了对于当中进行充值的判断(因为充值了钱会增加，不然到后面都是负数了),不过以上代码只是针对一张卡的