以下是我编写的一段数据统计程序,把 score_jibenchengji 中的数据统计到 score_total,作为其中一项成绩.再对Score_total 中几项成绩求平均,得出最后所需要的结果。
问题是:
1、这段程序运行速度比较慢,统计需要的时间比较长。请哪位高手指点一下,如何作一下修改。或您发现其中哪些地方可以加以改近,请你指教。
2、我用Sql2000存储数据,如何将通过计算得到的浮点数只保留两位小数存入数据库中,我用 (浮点数*100)取整/100的方法,但是用企业管理器查看是只存进了两位小数,但有查询分析器查看,小数位一大串,怎么回事,如何解决?我不清楚是否有这类操作的函数?
////////////////////////////////////////////////////////////////////////
implementation
uses dmdata;
{$R *.dfm}procedure TfmTotal.bt_JiBenTotalClick(Sender: TObject);
var
gonghao :string;
str:string;
score :single;
begin
if messagedlg('确定汇总此项成绩吗?',mtconfirmation,[mbyes,mbno],0)<>idyes then
exit;
try
if not dm.ADOConnection1.InTransaction then dm.ADOConnection1.BeginTrans ;
if dm.AquJibenChengJi.Active then dm.AquJibenChengJi.Close;
dm.AquJibenChengJi.Prepared :=false;
dm.AquJibenChengJi.SQL.Clear ;
dm.AquJibenChengJi.SQL.Add('select 工号,时间,雨季行车,非正常行车,故障处理,规章,防寒,业余学习,竞争上岗1,竞争上岗2,抽考1,抽考2 from score_jibenchengji where 时间='''+combobox1.Text+''' order by 工号 asc');
dm.AquJibenChengJi.Prepared:=true ;
dm.AquJibenChengJi.Open;
dm.AquJibenChengJi.First ; while not dm.AquJibenChengJi.Eof do
begin
gonghao := dm.AquJibenChengJi.FieldValues['工号'];
score :=jibenavg();//求几项成绩平均。
if dm.aquTemp.Active then dm.aqutemp.Close;
dm.aquTemp.SQL.Clear ;
dm.aquTemp.SQL.Add('select * from Score_total where 工号 = '''+gonghao+''' and 年份 = '''+combobox1.Text+'''');
dm.aquTemp.Open ; if dm.aquTemp.RecordCount <1 then
begin
acomd_total.Prepared :=false;
str:='insert into score_total(工号,年份,日常成绩) values(:gonghao1 ,:combobox1,:score1)';
acomd_total.Parameters.ParamByName('gonghao1').Value := gonghao;
acomd_total.Parameters.ParamByName('combobox1').Value := combobox1.Text;
acomd_total.Parameters.ParamByName('score1').Value := score;
acomd_total.CommandText :=str;
acomd_total.Prepared :=true;
acomd_total.Execute ;
end else
begin
acomd_total.Prepared :=false;
str:='update score_total set score_total.日常成绩 = :score1 where score_total.工号=:gonghao1 and 年份 = :combobox1';
acomd_total.Parameters.ParamByName('gonghao1').Value := gonghao;
acomd_total.Parameters.ParamByName('combobox1').Value := combobox1.Text;
acomd_total.Parameters.ParamByName('score1').Value := score;
acomd_total.CommandText :=str;
acomd_total.Prepared :=true;
acomd_total.Execute ;
end;
dm.Aqujibenchengji.Next ;
end;
if dm.ADOConnection1.InTransaction then
begin
dm.ADOConnection1.CommitTrans ;
showmessage('操作结果:日常考试数据统计成功!');
end;
except
showmessage('对不起,数据统计过程中出错,操作取消!');
dm.ADOConnection1.RollbackTrans ;
end;end;procedure TfmTotal.btCloseClick(Sender: TObject);
begin
if dm.AquJibenChengJi.Active then dm.AquJibenChengJi.Close ;
if dm.AquSanBai.Active then dm.AquSanBai.Close ;
if adsettotal.Active then adsettotal.Close ;
close;
end;function tfmtotal.JibenAvg():single;
var
score:single;
n:integer;
begin
score:=0;
n:=0;
with dm do
begin
if aqujibenchengji.FieldByName('雨季行车').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['雨季行车'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('非正常行车').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['非正常行车'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('故障处理').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['故障处理'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('规章').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['规章'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('防寒').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['防寒'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('业余学习').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['业余学习'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('竞争上岗1').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['竞争上岗1'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('竞争上岗2').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['竞争上岗2'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('抽考1').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['抽考1'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('抽考2').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['抽考2'];
n:=n+1;
end;
end;
try
score:=trunc(score/n*100)*0.01;
result:= score;
except
result:= 0;
end;
end;
procedure TfmTotal.bt_SumClick(Sender: TObject);
var
str:string;
temp:single;
n:integer;
begin
if messagedlg('注意:在[日常考试][三百考试][平稳操纵][触屏考试]单项'+#10+'汇总的基础上方可生成素质学分,继续吗?',mtconfirmation,[mbyes,mbno],0)<>idyes then
exit;
if adsettotal.Active then adsettotal.Close;
adsettotal.Prepared :=false ;
str:='select * from score_total where 年份='''+combobox1.Text+'''';
adsettotal.CommandText :=str;
adsettotal.Prepared:=true ;
adsettotal.Open;
adsettotal.First ;
//try
if not dm.ADOConnection1.InTransaction then dm.ADOConnection1.BeginTrans ;
while not adsettotal.Eof do
begin
temp:=0;
n:=0;
if adsettotal.FieldValues ['日常成绩']>0 then
begin
temp:=temp+adsettotal.FieldValues['日常成绩'];
n:=n+1;
end;
if adsettotal.FieldValues['三百成绩']>0 then
begin
temp:=temp+adsettotal.FieldValues['三百成绩'];
n:=n+1;
end;
try
temp:=trunc(temp/n*100)/100;//为了舍掉过多的小数,但用查询分析器查看,照样小数位一大串,如何做?
except
temp:=0;
end;
adsettotal.Edit;
adsettotal.FieldByName('汇总成绩').AsFloat :=temp;
adsettotal.UpdateBatch(); adsettotal.Next;
end; if dm.ADOConnection1.InTransaction then
dm.ADOConnection1.CommitTrans ;
showmessage('操作结果:数据统计成功!');
//except
//dm.ADOConnection1.RollbackTrans ;
//showmessage('操作结果:数据统计出错!');
//end;
end;
问题是:
1、这段程序运行速度比较慢,统计需要的时间比较长。请哪位高手指点一下,如何作一下修改。或您发现其中哪些地方可以加以改近,请你指教。
2、我用Sql2000存储数据,如何将通过计算得到的浮点数只保留两位小数存入数据库中,我用 (浮点数*100)取整/100的方法,但是用企业管理器查看是只存进了两位小数,但有查询分析器查看,小数位一大串,怎么回事,如何解决?我不清楚是否有这类操作的函数?
////////////////////////////////////////////////////////////////////////
implementation
uses dmdata;
{$R *.dfm}procedure TfmTotal.bt_JiBenTotalClick(Sender: TObject);
var
gonghao :string;
str:string;
score :single;
begin
if messagedlg('确定汇总此项成绩吗?',mtconfirmation,[mbyes,mbno],0)<>idyes then
exit;
try
if not dm.ADOConnection1.InTransaction then dm.ADOConnection1.BeginTrans ;
if dm.AquJibenChengJi.Active then dm.AquJibenChengJi.Close;
dm.AquJibenChengJi.Prepared :=false;
dm.AquJibenChengJi.SQL.Clear ;
dm.AquJibenChengJi.SQL.Add('select 工号,时间,雨季行车,非正常行车,故障处理,规章,防寒,业余学习,竞争上岗1,竞争上岗2,抽考1,抽考2 from score_jibenchengji where 时间='''+combobox1.Text+''' order by 工号 asc');
dm.AquJibenChengJi.Prepared:=true ;
dm.AquJibenChengJi.Open;
dm.AquJibenChengJi.First ; while not dm.AquJibenChengJi.Eof do
begin
gonghao := dm.AquJibenChengJi.FieldValues['工号'];
score :=jibenavg();//求几项成绩平均。
if dm.aquTemp.Active then dm.aqutemp.Close;
dm.aquTemp.SQL.Clear ;
dm.aquTemp.SQL.Add('select * from Score_total where 工号 = '''+gonghao+''' and 年份 = '''+combobox1.Text+'''');
dm.aquTemp.Open ; if dm.aquTemp.RecordCount <1 then
begin
acomd_total.Prepared :=false;
str:='insert into score_total(工号,年份,日常成绩) values(:gonghao1 ,:combobox1,:score1)';
acomd_total.Parameters.ParamByName('gonghao1').Value := gonghao;
acomd_total.Parameters.ParamByName('combobox1').Value := combobox1.Text;
acomd_total.Parameters.ParamByName('score1').Value := score;
acomd_total.CommandText :=str;
acomd_total.Prepared :=true;
acomd_total.Execute ;
end else
begin
acomd_total.Prepared :=false;
str:='update score_total set score_total.日常成绩 = :score1 where score_total.工号=:gonghao1 and 年份 = :combobox1';
acomd_total.Parameters.ParamByName('gonghao1').Value := gonghao;
acomd_total.Parameters.ParamByName('combobox1').Value := combobox1.Text;
acomd_total.Parameters.ParamByName('score1').Value := score;
acomd_total.CommandText :=str;
acomd_total.Prepared :=true;
acomd_total.Execute ;
end;
dm.Aqujibenchengji.Next ;
end;
if dm.ADOConnection1.InTransaction then
begin
dm.ADOConnection1.CommitTrans ;
showmessage('操作结果:日常考试数据统计成功!');
end;
except
showmessage('对不起,数据统计过程中出错,操作取消!');
dm.ADOConnection1.RollbackTrans ;
end;end;procedure TfmTotal.btCloseClick(Sender: TObject);
begin
if dm.AquJibenChengJi.Active then dm.AquJibenChengJi.Close ;
if dm.AquSanBai.Active then dm.AquSanBai.Close ;
if adsettotal.Active then adsettotal.Close ;
close;
end;function tfmtotal.JibenAvg():single;
var
score:single;
n:integer;
begin
score:=0;
n:=0;
with dm do
begin
if aqujibenchengji.FieldByName('雨季行车').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['雨季行车'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('非正常行车').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['非正常行车'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('故障处理').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['故障处理'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('规章').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['规章'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('防寒').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['防寒'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('业余学习').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['业余学习'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('竞争上岗1').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['竞争上岗1'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('竞争上岗2').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['竞争上岗2'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('抽考1').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['抽考1'];
n:=n+1;
end;
if aqujibenchengji.FieldByName('抽考2').AsFloat >0 then
begin
score:= score+aqujibenchengji.FieldValues['抽考2'];
n:=n+1;
end;
end;
try
score:=trunc(score/n*100)*0.01;
result:= score;
except
result:= 0;
end;
end;
procedure TfmTotal.bt_SumClick(Sender: TObject);
var
str:string;
temp:single;
n:integer;
begin
if messagedlg('注意:在[日常考试][三百考试][平稳操纵][触屏考试]单项'+#10+'汇总的基础上方可生成素质学分,继续吗?',mtconfirmation,[mbyes,mbno],0)<>idyes then
exit;
if adsettotal.Active then adsettotal.Close;
adsettotal.Prepared :=false ;
str:='select * from score_total where 年份='''+combobox1.Text+'''';
adsettotal.CommandText :=str;
adsettotal.Prepared:=true ;
adsettotal.Open;
adsettotal.First ;
//try
if not dm.ADOConnection1.InTransaction then dm.ADOConnection1.BeginTrans ;
while not adsettotal.Eof do
begin
temp:=0;
n:=0;
if adsettotal.FieldValues ['日常成绩']>0 then
begin
temp:=temp+adsettotal.FieldValues['日常成绩'];
n:=n+1;
end;
if adsettotal.FieldValues['三百成绩']>0 then
begin
temp:=temp+adsettotal.FieldValues['三百成绩'];
n:=n+1;
end;
try
temp:=trunc(temp/n*100)/100;//为了舍掉过多的小数,但用查询分析器查看,照样小数位一大串,如何做?
except
temp:=0;
end;
adsettotal.Edit;
adsettotal.FieldByName('汇总成绩').AsFloat :=temp;
adsettotal.UpdateBatch(); adsettotal.Next;
end; if dm.ADOConnection1.InTransaction then
dm.ADOConnection1.CommitTrans ;
showmessage('操作结果:数据统计成功!');
//except
//dm.ADOConnection1.RollbackTrans ;
//showmessage('操作结果:数据统计出错!');
//end;
end;
解决方案 »
- 50分打听一下 - 有谁知道这样的一个组件。xy坐标系(xy都是正负值,0是原点)就是一个+字样的。
- 发的问题没人答或者有人答也答的不对,那贴子还要不要结?
- Delphi连接数据库的时候出现登陆窗口怎么去掉?
- 救命呀数据库问题
- 如何将intraweb做的EXE文件发布到网上?
- 在DP6中如何控制状态栏中的字符?
- 如何查询值为空字符串的那些记录
- DELPHI中的第三方报表控件有没有类似PUWERBBUILDER数据窗的?
- 一个简单的问题,请教各位高手
- 我想在系统发出消息(比如有键按下、鼠标按下、建立窗口等)产生一个事件。该如何做?急,急,急!
- 一个简单的语句,不知道怎么写!:)
- 数据库中如何求最大值的问题????
with dm.AquJibenChengJi do
begin
end
能不能用Sql实现
首先你把你的一大堆sql逻辑写成存储过程会提高一些速度
第二个问题,你应在提交数据前就用formatfloat('#.00',floatdata)将数据格式化后再存入数据库
1。Code 比较乱,有一点命名规范,没有编码规范,不过流程还比较清晰;
查询不要使用*,通配符之类的东西,谁知道你有那些字段?
2。使用FormatFloat函数;
2、用sql语句select AVG(字段) into score_total form score_jibenchengji
select AVG(成绩1,成绩2,成绩3,成绩4) into 汇总 from table. 这做可以吧。???