请教大家做统计报表的时候SQL语句都是怎么写的
我最近在做几个报表,用的是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;