请教大家做统计报表的时候SQL语句都是怎么写的

966126 2004-04-13 02:53:44
我最近在做几个报表,用的是ADOQuery+Access+QuickReport
报表的每个空格的数据都是统计出来的,由于统计值为0的时候0不会显示出来,而是显示空的,所以所有的数据都是在报表显示前我自己用SQL统计后赋值过去的,但是我现在写的代码执行速度很慢,好像是ADOQuery在重复open的时候需要消耗大量时间,在记录数为0的时候也是如此,我现在是这样写的,大家看看有没好的办法能够提高执行速度

procedure TBlueCountForm.QRB11;
const
DQ:array [1..12] of string=('锦屏','岳林','溪口','萧王庙','江口','西坞','尚田','大堰','莼湖','裘村','松岙','');

var
i,j:integer;
begin
with DataModule1.ADOQuery_Count do
begin
for i:=1 to 12 do
begin
close;
sql.Clear;
sql.Add('select ');
sql.Add(' (select sum(ZMJ) from blue where SYZ like "%'+DQ[i]+'%" and DWXZ like "%全民所有%") as QMSY,');
sql.Add(' (select sum(ZMJ) from blue where SYZ like "%'+DQ[i]+'%" and DWXZ like "%集体所有%") as JTSY,');
sql.Add(' (select sum(ZMJ) from blue where SYZ like "%'+DQ[i]+'%" and DWXZ like "%个体所有%") as GTSY,');
sql.Add(' (select sum(SYMJ) from blue where SYZ like "%'+DQ[i]+'%") as SY,');
sql.Add(' (select sum(TTMJ) from blue where SYZ like "%'+DQ[i]+'%") as TT,');
sql.Add(' (select sum(ZMJ) from blue where SYZ like "%'+DQ[i]+'%" and BH like "Q%") as HS,');
sql.Add(' (select sum(ZMJ) from blue where SYZ like "%'+DQ[i]+'%" and BH like "S%") as DS');
sql.Add(' from blue');
open;

for j:=0 to BlueReport11.ComponentCount-1 do
begin
if (BlueReport11.Components[j] is TQRLabel) then
begin
if (BlueReport11.Components[j] as TQRLabel).Name='QMSY'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('QMSY').AsFloat);

if (BlueReport11.Components[j] as TQRLabel).Name='JTSY'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('JTSY').AsFloat);

if (BlueReport11.Components[j] as TQRLabel).Name='GTSY'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('GTSY').AsFloat);

if (BlueReport11.Components[j] as TQRLabel).Name='SY'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('SY').AsFloat);

if (BlueReport11.Components[j] as TQRLabel).Name='TT'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('TT').AsFloat);

if (BlueReport11.Components[j] as TQRLabel).Name='HS'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('HS').AsFloat);

if (BlueReport11.Components[j] as TQRLabel).Name='DS'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('DS').AsFloat);

if (BlueReport11.Components[j] as TQRLabel).Name='ZS'+IntToStr(i) then
(BlueReport11.Components[j] as TQRLabel).Caption:=FloatToStr(FieldByName('QMSY').AsFloat+FieldByName('JTSY').AsFloat+FieldByName('GTSY').AsFloat);
end;
end;

end;
end;

BlueReport11.PreviewModal;
end;
...全文
190 9 打赏 收藏 举报
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
966126 2004-04-14
用最早的方法,能通过优化sql语句来提高效率吗
  • 打赏
  • 举报
回复
966126 2004-04-14
发现在access的查询里运行如下语句的统计结果都是空的...
SELECT
(select sum(ZMJ) from blue where SYZ like "%锦屏%" and DWXZ like "%全民所有%") AS QMSY,
(select sum(ZMJ) from blue where SYZ like "%锦屏%" and DWXZ like "%集体所有%") AS JTSY,
(select sum(ZMJ) from blue where SYZ like "%锦屏%" and DWXZ like "%个体所有%") AS GTSY,
(select sum(SYMJ) from blue where SYZ like "%锦屏%") AS SY,
(select sum(TTMJ) from blue where SYZ like "%锦屏%") AS TT,
(select sum(ZMJ) from blue where SYZ like "%锦屏%" and BH like "Q%") AS HS,
(select sum(ZMJ) from blue where SYZ like "%锦屏%" and BH like "S%") AS DS
FROM blue;
  • 打赏
  • 举报
回复
966126 2004-04-14
存储过程里怎么用循环,如果每个报表都要写12个查询,那工作量实在太大了,我一共要做二十多张报表
如果按照SYZ字段的某几个字符分组查询的话要怎么写,也就是分组后第一组的特征是like "%aa%",第二组的特征是like "%bbb%"这样子
  • 打赏
  • 举报
回复
hwz_119 2004-04-14
access2000里应该用宏了吧,跟vb差不多
  • 打赏
  • 举报
回复
966126 2004-04-14
menggirl(看天上浮云,胜似闲庭信步):
请教存储过程要怎么写,我用的是access 2000
  • 打赏
  • 举报
回复
xiaosq2000 2004-04-13
建议将quickrep的数据集指向你要打印的数据集,改变你QRdbtext的mask属性,"0"即可
不过最方便的还是用FastReport,ReportMachine等报表控件来做
  • 打赏
  • 举报
回复
menggirl 2004-04-13
建议写在存贮过程里进行调用
  • 打赏
  • 举报
回复
qingke21 2004-04-13
  • 打赏
  • 举报
回复
hongama 2004-04-13
你现在用1条记录一条记录的判别修改就肯定慢了,建议将quickrep的数据集指向你要打印的数据集,改变你QRdbtext的mask属性,"0"即可
  • 打赏
  • 举报
回复
相关推荐
发帖
数据库相关

2474

社区成员

Delphi 数据库相关
社区管理员
  • 数据库相关社区
加入社区
帖子事件
创建了帖子
2004-04-13 02:53
社区公告
暂无公告