首先判断用户的查询条件如果 哪个查询条件为空则不加如到查询语句中 if 条件1<>'' then query1.sql.add('.......'); if 条件2<>''then query1.sql.add('......');
根据用户选择的条件,动态添加SQL语句!
查询分析器: 1.获得所有的字段名及其数据类型 2.如果多表查询的话建议用 SELECT......FROM T LEFT JION T2 ON...... 3.用一公共变量(da)存储你最基本的SQL语句,如SELECT A,B FROM T 4.可动态创建个TStringList用于保存条件,执行时把它的Strings[]取出append到da ......
//如果选择日期,加入日期条件 if cbbdate.Checked =true then StrSql:=StrSql+'and sddate between '+'#'+datetostr(dtpsfdate.Date)+ '#'+' and '+'#'+datetostr(dtpsfend.Date)+'#'; //如果选择客户 if cbkh1.Checked=true then begin adofind.SQL.Clear; adofind.SQL.Add('select * from khxx where khmc='''+cbkh.Text+''''); adofind.Open; StrSql:=StrSql+'and khdm='''+adofind.fieldbyname('khdm').AsString+''''; end; //如果选择表名 if cbbm1.Checked=true then begin adofind.SQL.Clear; adofind.SQL.Add('select * from khbh where khbm='''+cbbm.Text+''''); adofind.Open; StrSql:=StrSql+'and khbh='''+adofind.fieldbyname('khbh').AsString+''''; end; //如果选择收费员 if cbsfy1.Checked=true then begin adofind.SQL.Clear; adofind.SQL.Add('select * from czyb where czyxm='''+cbsfy.Text+''''); adofind.Open; StrSql:=StrSql+'and ysf='''+adofind.fieldbyname('czybm').AsString+''''; end; //如果选择开票员 if cbkpy1.Checked=true then begin adofind.SQL.Clear; adofind.SQL.Add('select * from czyb where czyxm='''+cbkpy.Text+''''); adofind.Open; StrSql:=StrSql+'and ykp='''+adofind.fieldbyname('czybm').AsString+''''; end; //如果是选择水费或者电费 if rbsf.Checked=true then begin StrSql:=StrSql+' and khbh like '''+'w%'+''''; end else begin StrSql:=StrSql+' and khbh like '''+'e%'+''''; //改变显示的标题 end; //开始查找数据 StrSql:=copy(StrSql,5,255); if StrSql='' then begin showmessage('请选择一个查询条件,在其前面打钩即可'); exit; end; with dmmain.ADOQKhsf do begin sql.Clear; sql.Add('select * from khsf where'); sql.Add(StrSql); open; end;
procedure TDevStationQuery.BtnSearchClick(Sender: TObject); Var lSql:string; begin lSql:=' where 1=1'; if Trim(RepeaterIDUp.Text)<>'' then lSql:=lSql+' and int(RepeaterID)>=int('''+Trim(RepeaterIDUp.Text)+''')'; if Trim(RepeaterIDDown.Text)<>'' then lSql:=lSql+' and int(RepeaterID)<=int('''+Trim(RepeaterIDDown.Text)+''')'; if Trim(SerIDUp.Text)<>'' then lSql:=lSql+' and SerID>='''+Trim(SerIDUp.Text)+''''; if Trim(SerIDDown.Text)<>'' then lSql:=lSql+' and SerID<='''+Trim(SerIDDown.Text)+''''; if Trim(EdtVerIDName.Text)<>'' then lSql:=lSql+' and VerID='''+EdtVerIDName.hint+''''; if DTFrmSetDate.EdtDateTime.Text<>'' then lSql:=lSql+' and SetDate='''+DTFrmSetDate.EdtDateTime.Text+''''; if Trim(StationName.Text)<>'' then lSql:=lSql+' and StationName like "%'+Trim(StationName.Text)+'%"'; if EdtChannelCode.Text<>'' then lSql:=lSql+' and ChannelCode='''+EdtChannelCode.hint+''''; if EdtProvince.Text<>'' then lSql:=lSql+' and Province='''+EdtProvince.hint+''''; if EdtCity.Text<>'' then lSql:=lSql+' and City='''+EdtCity.hint+''''; if Trim(LStationID.Text)<>'' then lSql:=lSql+' and LStationID='''+Trim(LStationID.Text)+''''; if EdtLStatSub.Text<>'' then lSql:=lSql+' and LStatSubId='''+EdtLStatSub.hint+''''; if Trim(Site.Text)<>'' then lSql:=lSql+' and Site like "%'+Trim(Site.Text)+'%"'; if EdtFactName.Text<>'' then lSql:=lSql+' and LfactoryMark='''+EdtFactName.hint+''''; if EdtHardName.Text<>'' then lSql:=lSql+' and LDeviceType='''+EdtHardName.hint+''''; //------------------------------------- if Trim(LDeviceVerr.Text)<>'' then lSql:=lSql+' and LDeviceVer='''+Trim(LDeviceVerr.Text)+''''; if Trim(LstatTel.Text)<>'' then lSql:=lSql+' and LstatTel='''+Trim(LstatTel.Text)+''''; if Trim(LqueryTel.Text)<>'' then lSql:=lSql+' and LqueryTel='''+Trim(LqueryTel.Text)+''''; if Trim(LalarmTel.Text)<>'' then lSql:=lSql+' and LalarmTel='''+Trim(LalarmTel.Text)+''''; FWhere:=lSql; Close;end;
可以用表的Filter和Filtered属性来过滤数据,而多条件组合查询可以参照下面的程序 Var FilterString:String; begin Try FilterString:=''; If CheckBox1.Checked then Begin If DateEdit1.Text=' - - ' then Begin Application.MessageBox('请输入过滤条件的起始时间!','提示',MB_OK OR MB_ICONEXCLAMATION); DateEdit1.SetFocus; Exit; End; If DateEdit2.Text=' - - ' then Begin Application.MessageBox('请输入过滤条件的终止时间!','提示',MB_OK OR MB_ICONEXCLAMATION); DateEdit2.SetFocus; Exit; End; FilterString:=FilterString+'MONEYPAIDDATE>='''+DateEdit1.Text+''' AND MONEYPAIDDATE<='''+DateEdit2.Text+''''; end; If CheckBox2.Checked then Begin If RxLookupEdit1.Text='' then Begin Application.MessageBox('请输入过滤条件的合同!','提示',MB_OK OR MB_ICONEXCLAMATION); RxLookupEdit1.SetFocus; Exit; End; If FilterString='' then FilterString:=FilterString+'MONEYPAIDCONTRACTCODE='''+RxLookupEdit1.Text+'''' Else FilterString:=FilterString+'AND MONEYPAIDCONTRACTCODE='''+RxLookupEdit1.Text+''''; end; If CheckBox3.Checked then Begin If RxLookupEdit2.Text='' then Begin Application.MessageBox('请输入过滤条件的受让方!','提示',MB_OK OR MB_ICONEXCLAMATION); RxLookupEdit2.SetFocus; Exit; End; If FilterString='' then FilterString:=FilterString+'MONEYPAIDCUSTOMERCODE='''+RxLookupEdit2.Text+'''' Else FilterString:=FilterString+'AND MONEYPAIDCUSTOMERCODE='''+RxLookupEdit2.Text+''''; end; DMStoreManage.TEXPIREMONEYPAID.Filter:=FilterString; DMStoreManage.TEXPIREMONEYPAID.Filtered:=True; Except End;
老兄,我也正在搞這個,我用的方法是這樣的: if Params.WarehouseName <> '' then begin SQL := SQL + ' AND (C.fWarehouseName = :fWarehouseName)'; dstStock.Parameters.ParamByName(fWarehouseName).Value := Params.WarehouseName; end;不過,我們技術老大一看,不行,這樣子來。你一下子可能搞不清楚的,我們老大有自己的好多接口與類以及方法,按他這樣子做,你在查詢時可以輸入"*"或"%",就像用Windows的查找一樣,我看懂了再交流吧。 if Params.ItemType <> sEMPTY then begin FieldName := 'A.' + sFIELD_ITEM_DESCR; C := TDBUtils.FormSearchByStrCondition(C); C := Format(sFORMAT_SELECT_FIELD_LIKE_STR, [FieldName, C]); Crit.AddANDCondition(C); end;
哪个查询条件为空则不加如到查询语句中
if 条件1<>'' then
query1.sql.add('.......');
if 条件2<>''then
query1.sql.add('......');
1.获得所有的字段名及其数据类型
2.如果多表查询的话建议用 SELECT......FROM T LEFT JION T2 ON......
3.用一公共变量(da)存储你最基本的SQL语句,如SELECT A,B FROM T
4.可动态创建个TStringList用于保存条件,执行时把它的Strings[]取出append到da
......
if cbbdate.Checked =true then
StrSql:=StrSql+'and sddate between '+'#'+datetostr(dtpsfdate.Date)+
'#'+' and '+'#'+datetostr(dtpsfend.Date)+'#';
//如果选择客户
if cbkh1.Checked=true then
begin
adofind.SQL.Clear;
adofind.SQL.Add('select * from khxx where khmc='''+cbkh.Text+'''');
adofind.Open;
StrSql:=StrSql+'and khdm='''+adofind.fieldbyname('khdm').AsString+'''';
end;
//如果选择表名
if cbbm1.Checked=true then
begin
adofind.SQL.Clear;
adofind.SQL.Add('select * from khbh where khbm='''+cbbm.Text+'''');
adofind.Open;
StrSql:=StrSql+'and khbh='''+adofind.fieldbyname('khbh').AsString+'''';
end;
//如果选择收费员
if cbsfy1.Checked=true then
begin
adofind.SQL.Clear;
adofind.SQL.Add('select * from czyb where czyxm='''+cbsfy.Text+'''');
adofind.Open;
StrSql:=StrSql+'and ysf='''+adofind.fieldbyname('czybm').AsString+'''';
end;
//如果选择开票员
if cbkpy1.Checked=true then
begin
adofind.SQL.Clear;
adofind.SQL.Add('select * from czyb where czyxm='''+cbkpy.Text+'''');
adofind.Open;
StrSql:=StrSql+'and ykp='''+adofind.fieldbyname('czybm').AsString+'''';
end;
//如果是选择水费或者电费
if rbsf.Checked=true then
begin
StrSql:=StrSql+' and khbh like '''+'w%'+'''';
end
else
begin
StrSql:=StrSql+' and khbh like '''+'e%'+'''';
//改变显示的标题
end;
//开始查找数据
StrSql:=copy(StrSql,5,255);
if StrSql='' then
begin
showmessage('请选择一个查询条件,在其前面打钩即可');
exit;
end;
with dmmain.ADOQKhsf do
begin
sql.Clear;
sql.Add('select * from khsf where');
sql.Add(StrSql);
open;
end;
Var lSql:string;
begin
lSql:=' where 1=1';
if Trim(RepeaterIDUp.Text)<>'' then
lSql:=lSql+' and int(RepeaterID)>=int('''+Trim(RepeaterIDUp.Text)+''')';
if Trim(RepeaterIDDown.Text)<>'' then
lSql:=lSql+' and int(RepeaterID)<=int('''+Trim(RepeaterIDDown.Text)+''')';
if Trim(SerIDUp.Text)<>'' then
lSql:=lSql+' and SerID>='''+Trim(SerIDUp.Text)+'''';
if Trim(SerIDDown.Text)<>'' then
lSql:=lSql+' and SerID<='''+Trim(SerIDDown.Text)+'''';
if Trim(EdtVerIDName.Text)<>'' then
lSql:=lSql+' and VerID='''+EdtVerIDName.hint+'''';
if DTFrmSetDate.EdtDateTime.Text<>'' then
lSql:=lSql+' and SetDate='''+DTFrmSetDate.EdtDateTime.Text+'''';
if Trim(StationName.Text)<>'' then
lSql:=lSql+' and StationName like "%'+Trim(StationName.Text)+'%"';
if EdtChannelCode.Text<>'' then
lSql:=lSql+' and ChannelCode='''+EdtChannelCode.hint+'''';
if EdtProvince.Text<>'' then
lSql:=lSql+' and Province='''+EdtProvince.hint+'''';
if EdtCity.Text<>'' then
lSql:=lSql+' and City='''+EdtCity.hint+'''';
if Trim(LStationID.Text)<>'' then
lSql:=lSql+' and LStationID='''+Trim(LStationID.Text)+'''';
if EdtLStatSub.Text<>'' then
lSql:=lSql+' and LStatSubId='''+EdtLStatSub.hint+'''';
if Trim(Site.Text)<>'' then
lSql:=lSql+' and Site like "%'+Trim(Site.Text)+'%"';
if EdtFactName.Text<>'' then
lSql:=lSql+' and LfactoryMark='''+EdtFactName.hint+'''';
if EdtHardName.Text<>'' then
lSql:=lSql+' and LDeviceType='''+EdtHardName.hint+'''';
//-------------------------------------
if Trim(LDeviceVerr.Text)<>'' then
lSql:=lSql+' and LDeviceVer='''+Trim(LDeviceVerr.Text)+'''';
if Trim(LstatTel.Text)<>'' then
lSql:=lSql+' and LstatTel='''+Trim(LstatTel.Text)+'''';
if Trim(LqueryTel.Text)<>'' then
lSql:=lSql+' and LqueryTel='''+Trim(LqueryTel.Text)+'''';
if Trim(LalarmTel.Text)<>'' then
lSql:=lSql+' and LalarmTel='''+Trim(LalarmTel.Text)+'''';
FWhere:=lSql;
Close;end;
Var
FilterString:String;
begin
Try
FilterString:='';
If CheckBox1.Checked then
Begin
If DateEdit1.Text=' - - ' then
Begin
Application.MessageBox('请输入过滤条件的起始时间!','提示',MB_OK OR MB_ICONEXCLAMATION);
DateEdit1.SetFocus;
Exit;
End;
If DateEdit2.Text=' - - ' then
Begin
Application.MessageBox('请输入过滤条件的终止时间!','提示',MB_OK OR MB_ICONEXCLAMATION);
DateEdit2.SetFocus;
Exit;
End;
FilterString:=FilterString+'MONEYPAIDDATE>='''+DateEdit1.Text+''' AND MONEYPAIDDATE<='''+DateEdit2.Text+'''';
end;
If CheckBox2.Checked then
Begin
If RxLookupEdit1.Text='' then
Begin
Application.MessageBox('请输入过滤条件的合同!','提示',MB_OK OR MB_ICONEXCLAMATION);
RxLookupEdit1.SetFocus;
Exit;
End;
If FilterString='' then
FilterString:=FilterString+'MONEYPAIDCONTRACTCODE='''+RxLookupEdit1.Text+''''
Else
FilterString:=FilterString+'AND MONEYPAIDCONTRACTCODE='''+RxLookupEdit1.Text+'''';
end;
If CheckBox3.Checked then
Begin
If RxLookupEdit2.Text='' then
Begin
Application.MessageBox('请输入过滤条件的受让方!','提示',MB_OK OR MB_ICONEXCLAMATION);
RxLookupEdit2.SetFocus;
Exit;
End;
If FilterString='' then
FilterString:=FilterString+'MONEYPAIDCUSTOMERCODE='''+RxLookupEdit2.Text+''''
Else
FilterString:=FilterString+'AND MONEYPAIDCUSTOMERCODE='''+RxLookupEdit2.Text+'''';
end;
DMStoreManage.TEXPIREMONEYPAID.Filter:=FilterString;
DMStoreManage.TEXPIREMONEYPAID.Filtered:=True;
Except End;
if Params.WarehouseName <> '' then
begin
SQL := SQL + ' AND (C.fWarehouseName = :fWarehouseName)';
dstStock.Parameters.ParamByName(fWarehouseName).Value := Params.WarehouseName;
end;不過,我們技術老大一看,不行,這樣子來。你一下子可能搞不清楚的,我們老大有自己的好多接口與類以及方法,按他這樣子做,你在查詢時可以輸入"*"或"%",就像用Windows的查找一樣,我看懂了再交流吧。
if Params.ItemType <> sEMPTY then
begin
FieldName := 'A.' + sFIELD_ITEM_DESCR;
C := TDBUtils.FormSearchByStrCondition(C);
C := Format(sFORMAT_SELECT_FIELD_LIKE_STR, [FieldName, C]);
Crit.AddANDCondition(C);
end;