//数据清零
with sjcx do
begin
close;
sql.Clear;
sql.Add('delete from sc_xsrb');
ExecSQL;
end;
sc_cjrb.Close;
sc_cjrb.Open;
//提取细纱工序开台及累计数据
with sjcx do
begin
close;
sql.Clear;
sql.Add('select pzmc,bb,sum(hjzl) hj from xscl where scrq>='''+datetostr(rq1.DateTime)+''' and scrq<='''+datetostr(rq2.DateTime)+''' and bmmc='''+trim(bmmc.Text)+''' group by pzmc,bb');
open;
end;
//插入累计产量 sjcx.First;
while not sjcx.Eof do
begin
with cx do
begin
close;
sql.Clear;
sql.Add('insert into sc_xsrb (rb_bb,rb_gx,rb_pzmc,rb_ljcl) values (:bb1,:gx,:pzmc1,:ljcl1)');
Parameters.ParamByName('bb1').Value:=sjcx.fieldbyname('bb').AsString;
Parameters.ParamByName('pzmc1').Value:=sjcx.fieldbyname('pzmc').AsString;
Parameters.ParamByName('ljcl1').Value:=sjcx.fieldbyname('hj').Value;
Parameters.ParamByName('gx').Value:='细纱';
ExecSQL;
end;
sjcx.Next;
end;
sc_cjrb.Close;
sc_cjrb.Open; //提取细纱工序开台及当日数据
with sjcx do
begin
close;
sql.Clear;
sql.Add('select pzmc,bb,count(*) kt,sum(hjzl) hj from xscl where scrq='''+datetostr(rq2.DateTime)+''' and bmmc='''+trim(bmmc.Text)+''' group by pzmc,bb');
open;
end;
//更新车间日报中的细纱产量
sc_cjrb.First;
while not sc_cjrb.Eof do
begin
sjcx.First;
while not sjcx.Eof do
begin
with cx do
begin
close;
sql.Clear;
sql.Add('update sc_xsrb set rb_drcl=:drcl1,rb_drkt=:drkt1 where rb_bb=:bb1 and rb_pzmc=:pzmc1 and rb_gx=''细纱''');
Parameters.ParamByName('bb1').Value:=sjcx.fieldbyname('bb').AsString;
Parameters.ParamByName('pzmc1').Value:=sjcx.fieldbyname('pzmc').AsString;
Parameters.ParamByName('drkt1').Value:=sjcx.fieldbyname('kt').Value;
Parameters.ParamByName('drcl1').Value:=sjcx.fieldbyname('hj').Value;
ExecSQL;
end;
sjcx.Next;
end;
sc_cjrb.Next;
end;以上代码执行速度较慢,请帮忙优化一下
with sjcx do
begin
close;
sql.Clear;
sql.Add('delete from sc_xsrb');
ExecSQL;
end;
sc_cjrb.Close;
sc_cjrb.Open;
//提取细纱工序开台及累计数据
with sjcx do
begin
close;
sql.Clear;
sql.Add('select pzmc,bb,sum(hjzl) hj from xscl where scrq>='''+datetostr(rq1.DateTime)+''' and scrq<='''+datetostr(rq2.DateTime)+''' and bmmc='''+trim(bmmc.Text)+''' group by pzmc,bb');
open;
end;
//插入累计产量 sjcx.First;
while not sjcx.Eof do
begin
with cx do
begin
close;
sql.Clear;
sql.Add('insert into sc_xsrb (rb_bb,rb_gx,rb_pzmc,rb_ljcl) values (:bb1,:gx,:pzmc1,:ljcl1)');
Parameters.ParamByName('bb1').Value:=sjcx.fieldbyname('bb').AsString;
Parameters.ParamByName('pzmc1').Value:=sjcx.fieldbyname('pzmc').AsString;
Parameters.ParamByName('ljcl1').Value:=sjcx.fieldbyname('hj').Value;
Parameters.ParamByName('gx').Value:='细纱';
ExecSQL;
end;
sjcx.Next;
end;
sc_cjrb.Close;
sc_cjrb.Open; //提取细纱工序开台及当日数据
with sjcx do
begin
close;
sql.Clear;
sql.Add('select pzmc,bb,count(*) kt,sum(hjzl) hj from xscl where scrq='''+datetostr(rq2.DateTime)+''' and bmmc='''+trim(bmmc.Text)+''' group by pzmc,bb');
open;
end;
//更新车间日报中的细纱产量
sc_cjrb.First;
while not sc_cjrb.Eof do
begin
sjcx.First;
while not sjcx.Eof do
begin
with cx do
begin
close;
sql.Clear;
sql.Add('update sc_xsrb set rb_drcl=:drcl1,rb_drkt=:drkt1 where rb_bb=:bb1 and rb_pzmc=:pzmc1 and rb_gx=''细纱''');
Parameters.ParamByName('bb1').Value:=sjcx.fieldbyname('bb').AsString;
Parameters.ParamByName('pzmc1').Value:=sjcx.fieldbyname('pzmc').AsString;
Parameters.ParamByName('drkt1').Value:=sjcx.fieldbyname('kt').Value;
Parameters.ParamByName('drcl1').Value:=sjcx.fieldbyname('hj').Value;
ExecSQL;
end;
sjcx.Next;
end;
sc_cjrb.Next;
end;以上代码执行速度较慢,请帮忙优化一下
//插入累计产量
这两步应该用一条SQL就可以实现了,可以这样写
sql.Text := 'insert into sc_xsrb (rb_bb,rb_gx,rb_pzmc,rb_ljcl) select bb,''细纱'',pzmc,sum(hjzl) hj from xscl where scrq>='''+datetostr(rq1.DateTime)+''' and scrq<='''+datetostr(rq2.DateTime)+''' and bmmc='''+trim(bmmc.Text)+''' group by pzmc,bb'
//提取细纱工序开台及当日数据
//更新车间日报中的细纱产量
这两步也可就应该可以用类似的方法实现
楼主将你那段
with adoquery1 do
begin
close;
...
open;
while not eof do
begin
...
end;
end;
代码更改为:with adoquery1 do
begin
close;
sql.clear;
sql.text := 'update a set a.f1 = b.ff1,a.f2 = b.ff2 ... from table1 a,table2 b where ...';
parameters.parambyname().value := ?;
execsql;
end;可能要好些;