以下代码你参考以下,希望对你有用;
dt:=formatdatetime('yyyy-mm-dd',datetimepicker1.Date);
liststr:=tstringlist.Create;
datamodule2.ADOConnection1.GetTableNames(liststr);
if liststr.IndexOf('shuru')<>-1 then
begin
datamodule2.ADOCommand1.CommandText:='drop view shuru';
datamodule2.ADOCommand1.Execute;
end;
if liststr.IndexOf('shuru1')<>-1 then
begin
datamodule2.ADOCommand1.CommandText:='drop view shuru1';
datamodule2.ADOCommand1.Execute;
end;
if liststr.IndexOf('shuru2')<>-1 then
begin
datamodule2.ADOCommand1.CommandText:='drop view shuru2';
datamodule2.ADOCommand1.Execute;
end;
if liststr.IndexOf('shuru3')<>-1 then
begin
datamodule2.ADOCommand1.CommandText:='drop view shuru3';
datamodule2.ADOCommand1.Execute;
end;
if liststr.IndexOf('shuru4')<>-1 then
begin
datamodule2.ADOCommand1.CommandText:='drop view shuru4';
datamodule2.ADOCommand1.Execute;
end;
datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('create view shuru1 as select 受理处,输入人员工号,sim卡号 from enternet where 登记日期='''+dt+''' ');
datamodule2.ADOshuruliang.ExecSQL;
datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('create view shuru2 as select distinct 输入人员工号,count(*) as 数量 from shuru1 group by all 输入人员工号');
datamodule2.ADOshuruliang.ExecSQL;
datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('create view shuru3 as select distinct 受理处, 输入人员工号, sum(卡费) as 卡费 from shuru1,charge where sim卡号=卡号 group by all 受理处,输入人员工号');
datamodule2.ADOshuruliang.ExecSQL;
//
{datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('create view shuru as select 受理处,输入人员工号, sum(卡费) as 卡费,count(*) as 数量 from enternet,charge where sim卡号=卡号 and 登记日期='''+dt+''' group by all 受理处,输入人员工号');
datamodule2.ADOshuruliang.ExecSQL;
}
//
datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('create view shuru4 as select shuru3.受理处 as 受理处 ,shuru3.输入人员工号 as 输入人员工号,数量,卡费 from shuru2,shuru3 where shuru2.输入人员工号=shuru3.输入人员工号');
datamodule2.ADOshuruliang.ExecSQL;
//
datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('select * from shuru4');
datamodule2.ADOshuruliang.Open;
if datamodule2.ADOshuruliang.RecordCount=0 then
begin
messagebox(handle,'没有符合条件的记录!','开户中心',mb_ok);
exit;
end;
//
datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('select sum(卡费) as 卡费合计 ,sum(数量) as 数量合计 from shuru4 ');
datamodule2.ADOshuruliang.Open;
try
kafeihj:=datamodule2.ADOshuruliang['卡费合计'];
except
kafeihj:=0;
end;
try
shulianghj:=datamodule2.ADOshuruliang['数量合计'];
except
shulianghj:=0;
end;
//
datamodule2.ADOshuruliang.Close;
datamodule2.ADOshuruliang.SQL.Clear;
datamodule2.ADOshuruliang.SQL.Add('select * from shuru4');
datamodule2.ADOshuruliang.Open;
quickrep1.DataSet:=datamodule2.ADOshuruliang;
QRDBText1.DataSet:=datamodule2.ADOshuruliang;
QRDBText1.DataField:='输入人员工号';
QRDBText2.DataSet:=datamodule2.ADOshuruliang;
QRDBText2.DataField:='卡费';
QRDBText3.DataSet:=datamodule2.ADOshuruliang;
QRDBText3.DataField:='数量';
QRDBText4.DataSet:=datamodule2.ADOshuruliang;
QRDBText4.DataField:='受理处';
QRLabel9.Caption:=floattostr(kafeihj);
QRLabel10.Caption:=floattostr(shulianghj);
quickrep1.Preview;