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

[966126]

我最近在做几个报表，用的是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;

[hongama]

你现在用1条记录一条记录的判别修改就肯定慢了，建议将quickrep的数据集指向你要打印的数据集，改变你QRdbtext的mask属性，"0"即可

• 打赏
• 举报

回复 赞

[qingke21]

顶

• 打赏
• 举报

回复 赞

[menggirl]

建议写在存贮过程里进行调用

• 打赏
• 举报

回复 赞

[xiaosq2000]

建议将quickrep的数据集指向你要打印的数据集，改变你QRdbtext的mask属性，"0"即可
不过最方便的还是用FastReport,ReportMachine等报表控件来做

• 打赏
• 举报

回复 赞

[966126]

menggirl(看天上浮云，胜似闲庭信步):
请教存储过程要怎么写，我用的是access 2000

• 打赏
• 举报

回复 赞

[hwz_119]

access2000里应该用宏了吧，跟vb差不多

• 打赏
• 举报

回复 赞

[966126]

存储过程里怎么用循环，如果每个报表都要写12个查询，那工作量实在太大了，我一共要做二十多张报表
如果按照SYZ字段的某几个字符分组查询的话要怎么写，也就是分组后第一组的特征是like "%aa%",第二组的特征是like "%bbb%"这样子

• 打赏
• 举报

回复 赞

[966126]

发现在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]

用最早的方法，能通过优化sql语句来提高效率吗

• 打赏
• 举报

回复 赞