var str:string;if edit1.text <> '' then str := str + 条件1 else if edit2.text <> '' then str := str + 条件2 . . . 把str 和 sql.text结合起来组成sql语句
var str:string; if edit1.text <> '' then str := str + edit1.text; if edit2.text <> '' then str := str+' and ' + edit2.text; if edit3.text <> '' then str := str+' and ' + edit3.text;
实在不行,如果你的代码逻辑里允许,如果SQL不出错,就使用Trim吧,为空就略过去。
procedure TForm1.Button1Click(Sender: TObject); const MaxCount = 3; var sInput : array [1..MaxCount] of string; sDef : array [1..MaxCount] of string; function getSql: string; var i : integer; begin result := 'select * from tb where 1 = 1'; sDef[1] := ' AND COL1 = %S'; sDef[2] := ' AND COL2 = %S'; sDef[3] := ' AND COL3 = %S'; for i := 1 to MaxCount do sInput[i] := ''; sInput[1] := edit1.text; sInput[2] := edit2.text; sInput[3] := edit3.text; for i := 1 to MaxCount do begin if trim(sInput[i]) = '' then continue; //如果需要对sInput[i]中的值加判断(防注入等),写在这里 result := result + format(sDef[i], [QuotedStr(sInput[i])]); end; end; begin showmessage(getsql); end;
一下是我的代碼,希望對你有幫助.procedure Tqueryfrm.Button3Click(Sender: TObject); function AddPercent(sSource:string):string; begin result := sSource; if Pos('%',sSource)=0 then Result := sSource+'%'; end; var sqlstr,swhere:string; begin sqlstr:='Select PQI_NUMBER,PQI_SUPPLIER,PQI_OSPOP,PQI_CREATER,PQI_CREATEDATE,PQI_CURRENCY,PQI_Status,PQI_TEXT,PQI_ID From tblospquota where PQI_NUMBER in('; sqlstr:=sqlstr+'Select distinct PQI_NUMBER From tblospquota A,tblospquotadt B where A.PQI_ID=B.PQI_ID(+) '; swhere:=''; if combobox1.Text <> '' then sWhere := sWhere + format(' AND A.PQI_NUMBER LIKE''%s'' ', [ AddPercent(combobox1.Text) ]); if combobox2.Text <> '' then sWhere := sWhere + format(' AND A.PQI_SUPPLIER LIKE ''%s'' ', [ AddPercent(suppler) ]); if combobox3.Text <> '' then sWhere := sWhere + format(' AND A.PQI_OSPOP LIKE ''%s'' ', [ AddPercent(combobox3.Text) ]); if combobox4.Text <> '' then sWhere := sWhere + format(' AND B.QID_ITM_CODE LIKE ''%s'' ', [ AddPercent(combobox4.Text) ]); if radiogroup1.ItemIndex<>-1 then sWhere := sWhere + format(' AND A.PQI_STATUS LIKE ''%s'' ', [ AddPercent(status) ]); sqlstr:=sqlstr+swhere; sqlstr:=sqlstr+')'; with quota.quotafrm.ADOQuery1 do begin close; sql.Clear; sql.Add(sqlstr); open; end; close; end;
下面是我认为最简洁实用的代码。 var str1,str2,str3:string; begin if Edit1.Text='' then str1:='' else str1:=' and FieldName='''+Edit1.Text+''''; if Edit2.Text='' then str2:='' else str2:=' and FieldName='''+Edit2.Text+''''; if Edit3.Text='' then str3:='' else str3:=' and FieldName='''+Edit3.Text+''''; ADOQuery.Sql.Text:='select * from TableName where 1=1'+str1+str2+str3;
字段A,B,C,分别对应edit1,edit2,edit3 with adoquery1 do begin close; sql.text:= 'select * from 表 where a=ifnull(:a,a) and b=ifnull(:b,b) and c=ifnull(c:,c)'; if edit1.text<>'' then ParamByName('a').Value:= edit1.text; if edit2.text<>'' then ParamByName('b').Value:= edit2.text; if edit3.text<>'' then ParamByName('c').Value:= edit3.text; open; end;
不用IF把 sql.text:= 'select * from 表 where a=ifnull(:a,a) and b=ifnull(:b,b) and c=ifnull(c:,c)'; 改成 sql.text:= 'select * from 表 where a=case when :a='''' then a else :a end and '+ 'b=case when :b='''' then b else :b end and c=case when :c='''' then c else :c end'; ParamByName('a').Value:= edit1.text; ParamByName('b').Value:= edit2.text; ParamByName('c').Value:= edit3.text; open; 不同的数据库系统CASE语句写法会一样。大体雷同。
仅供参考 function TForm1.MakeSQL: string; begin Result := 'select * from T where 1=1 and F1='''+Edit1.Text+''' and F2=''' +Edit2.Text+''' and F3='''+Edit3.Text+''''; Result := StringReplace(Result, ' and F1=''''', '', []); Result := StringReplace(Result, ' and F2=''''', '', []); Result := StringReplace(Result, ' and F3=''''', '', []); end;
改良一下 function MakeSQL: string; begin Result := 'select * from T where 1=1 and F1='+QuotedStr(Edit1.Text)+' and F2=' +QuotedStr(Edit2.Text)+' and F3='+QuotedStr(Edit3.Text)+' '; Result := StringReplace(Result, 'and F1='''' ', '', []); Result := StringReplace(Result, 'and F2='''' ', '', []); Result := StringReplace(Result, 'and F3='''' ', '', []); end;
还可以更简单的方法var sWhere:string; begin if trim(edit1.text)<>'' then sWhere:=' and F1='''+trim(edit1.text)+''''; if trim(edit2.text)<>'' then sWhere:=sWhere+' and F2='''+trim(edit2.text)+''''; if trim(edit3.text)<>'' then sWhere:=sWhere+' and F3='''+trim(edit3.text)+''''; qry1.close; qry1.sql.text:='select * from table where 1=1'+sWhere; qry1.open; end;
var strSQL,strCondition:string; bolFlag:boolean; begin strCondition:=' WHERE '; strSQL:='SELECT * FROM TABEL'; bolFlag:=False; if Trim(edt1.text)<>'' then begin strSQL:=strSQL+strCondition +' Field =:Field1'; strCondition:=' AND '; bolFlag:=True; end; if Trim(edt2.text)<>'' then begin strSQL:=strSQL+strCondition +' Field =:Field2'; strCondition:=' AND '; bolFlag:=True; end; ... if not bolFlag then begin MessageDlg('請輸入查詢條件',mtinformation,[mbyes],0); Exit; end; with dtset do begin close; CommandText:=strSQL; if Trim(edt1.text)<>'' then Parameters.ParamByName('Field1').Value := Field1; if Trim(edt2.text)<>'' then Parameters.ParamByName('Field2').Value := Field2; ... open; DtSource.DataSet := DataSet ; DBGrid.DataSource := DataSet; end; end;
可以把需要判断的TEdit的Hint属性写上中文字段名称 然后判断:var i:integer; for i:= 0 to self.ComponentCount-1 do with self.components[i] do if self.components[i] is TEdit then if Length(Trim(TEdit(frm.Components[i]).EditText))=0 then begin MessageBox(self.Handle, PChar('“'+TEdit(self.Components[i]).Hint+'”必须输入,不可以为空!'), PChar('提示'),MB_ICONINFORMATION or MB_OK); TEdit(self.Components[i]).SetFocus; exit; end;
可以把需要判断的TEdit的Hint属性写上中文字段名称 然后判断:var i:integer; for i:= 0 to self.ComponentCount-1 do with self.components[i] do if self.components[i] is TEdit then if Length(Trim(TEdit(frm.Components[i]).EditText))=0 then begin MessageBox(self.Handle, PChar('“'+TEdit(self.Components[i]).Hint+'”必须输入,不可以为空!'), PChar('提示'),MB_ICONINFORMATION or MB_OK); TEdit(self.Components[i]).SetFocus; exit; end;
先把select部分写好,where后面跟 1 = 1, 后面的条件都用 and + edit.text等等
SQL:='SELECT * from table1 where 1>0'; if edit1<>'' then SQL:=SQL+' and 条件1= '+edit1.text; if edit2<>'' then SQL:=SQL+' and 条件2= '+edit1.text; if edit3<>'' then SQL:=SQL+' and 条件3= '+edit1.text; 可以任意组合任意多个条件,技巧就是先设置 select 语句中得where条件为真,然后把and加到后面得条件上去。
str:string;if edit1.text <> '' then
str := str + 条件1
else if edit2.text <> '' then
str := str + 条件2
.
.
.
把str 和 sql.text结合起来组成sql语句
str:string; if edit1.text <> '' then
str := str + edit1.text;
if edit2.text <> '' then
str := str+' and ' + edit2.text;
if edit3.text <> '' then
str := str+' and ' + edit3.text;
procedure TForm1.Button1Click(Sender: TObject);
const
MaxCount = 3;
var
sInput : array [1..MaxCount] of string;
sDef : array [1..MaxCount] of string;
function getSql: string;
var
i : integer;
begin
result := 'select * from tb where 1 = 1';
sDef[1] := ' AND COL1 = %S';
sDef[2] := ' AND COL2 = %S';
sDef[3] := ' AND COL3 = %S'; for i := 1 to MaxCount do
sInput[i] := '';
sInput[1] := edit1.text;
sInput[2] := edit2.text;
sInput[3] := edit3.text; for i := 1 to MaxCount do
begin
if trim(sInput[i]) = '' then continue;
//如果需要对sInput[i]中的值加判断(防注入等),写在这里
result := result + format(sDef[i], [QuotedStr(sInput[i])]);
end;
end;
begin
showmessage(getsql);
end;
你这个好像不行吧?如果第一个就不等空,那不就执行str := str + 条件1 就没了。不是还有当第一个不为空,第两个,或第三个也不为空的情况吗?
function AddPercent(sSource:string):string;
begin
result := sSource;
if Pos('%',sSource)=0 then Result := sSource+'%';
end;
var sqlstr,swhere:string;
begin
sqlstr:='Select PQI_NUMBER,PQI_SUPPLIER,PQI_OSPOP,PQI_CREATER,PQI_CREATEDATE,PQI_CURRENCY,PQI_Status,PQI_TEXT,PQI_ID From tblospquota where PQI_NUMBER in(';
sqlstr:=sqlstr+'Select distinct PQI_NUMBER From tblospquota A,tblospquotadt B where A.PQI_ID=B.PQI_ID(+) ';
swhere:='';
if combobox1.Text <> '' then sWhere := sWhere + format(' AND A.PQI_NUMBER LIKE''%s'' ', [ AddPercent(combobox1.Text) ]);
if combobox2.Text <> '' then sWhere := sWhere + format(' AND A.PQI_SUPPLIER LIKE ''%s'' ', [ AddPercent(suppler) ]);
if combobox3.Text <> '' then sWhere := sWhere + format(' AND A.PQI_OSPOP LIKE ''%s'' ', [ AddPercent(combobox3.Text) ]);
if combobox4.Text <> '' then sWhere := sWhere + format(' AND B.QID_ITM_CODE LIKE ''%s'' ', [ AddPercent(combobox4.Text) ]);
if radiogroup1.ItemIndex<>-1 then sWhere := sWhere + format(' AND A.PQI_STATUS LIKE ''%s'' ', [ AddPercent(status) ]);
sqlstr:=sqlstr+swhere;
sqlstr:=sqlstr+')';
with quota.quotafrm.ADOQuery1 do
begin
close;
sql.Clear;
sql.Add(sqlstr);
open;
end;
close;
end;
var str1,str2,str3:string;
begin
if Edit1.Text='' then
str1:=''
else
str1:=' and FieldName='''+Edit1.Text+'''';
if Edit2.Text='' then
str2:=''
else
str2:=' and FieldName='''+Edit2.Text+'''';
if Edit3.Text='' then
str3:=''
else
str3:=' and FieldName='''+Edit3.Text+'''';
ADOQuery.Sql.Text:='select * from TableName where 1=1'+str1+str2+str3;
with adoquery1 do
begin
close;
sql.text:= 'select * from 表 where a=ifnull(:a,a) and b=ifnull(:b,b) and c=ifnull(c:,c)';
if edit1.text<>'' then
ParamByName('a').Value:= edit1.text;
if edit2.text<>'' then
ParamByName('b').Value:= edit2.text;
if edit3.text<>'' then
ParamByName('c').Value:= edit3.text;
open;
end;
sql.text:= 'select * from 表 where a=ifnull(:a,a) and b=ifnull(:b,b) and c=ifnull(c:,c)';
改成
sql.text:= 'select * from 表 where a=case when :a='''' then a else :a end and '+
'b=case when :b='''' then b else :b end and c=case when :c='''' then c else :c end';
ParamByName('a').Value:= edit1.text;
ParamByName('b').Value:= edit2.text;
ParamByName('c').Value:= edit3.text;
open; 不同的数据库系统CASE语句写法会一样。大体雷同。
function TForm1.MakeSQL: string;
begin
Result := 'select * from T where 1=1 and F1='''+Edit1.Text+''' and F2='''
+Edit2.Text+''' and F3='''+Edit3.Text+'''';
Result := StringReplace(Result, ' and F1=''''', '', []);
Result := StringReplace(Result, ' and F2=''''', '', []);
Result := StringReplace(Result, ' and F3=''''', '', []);
end;
function MakeSQL: string;
begin
Result := 'select * from T where 1=1 and F1='+QuotedStr(Edit1.Text)+' and F2='
+QuotedStr(Edit2.Text)+' and F3='+QuotedStr(Edit3.Text)+' ';
Result := StringReplace(Result, 'and F1='''' ', '', []);
Result := StringReplace(Result, 'and F2='''' ', '', []);
Result := StringReplace(Result, 'and F3='''' ', '', []);
end;
sWhere:string;
begin
if trim(edit1.text)<>'' then
sWhere:=' and F1='''+trim(edit1.text)+'''';
if trim(edit2.text)<>'' then
sWhere:=sWhere+' and F2='''+trim(edit2.text)+'''';
if trim(edit3.text)<>'' then
sWhere:=sWhere+' and F3='''+trim(edit3.text)+'''';
qry1.close;
qry1.sql.text:='select * from table where 1=1'+sWhere;
qry1.open;
end;
strSQL,strCondition:string;
bolFlag:boolean;
begin
strCondition:=' WHERE ';
strSQL:='SELECT * FROM TABEL';
bolFlag:=False;
if Trim(edt1.text)<>'' then
begin
strSQL:=strSQL+strCondition +' Field =:Field1';
strCondition:=' AND ';
bolFlag:=True;
end;
if Trim(edt2.text)<>'' then
begin
strSQL:=strSQL+strCondition +' Field =:Field2';
strCondition:=' AND ';
bolFlag:=True;
end;
...
if not bolFlag then
begin
MessageDlg('請輸入查詢條件',mtinformation,[mbyes],0);
Exit;
end;
with dtset do
begin
close;
CommandText:=strSQL;
if Trim(edt1.text)<>'' then
Parameters.ParamByName('Field1').Value := Field1;
if Trim(edt2.text)<>'' then
Parameters.ParamByName('Field2').Value := Field2;
...
open;
DtSource.DataSet := DataSet ;
DBGrid.DataSource := DataSet;
end;
end;
然后判断:var
i:integer;
for i:= 0 to self.ComponentCount-1 do
with self.components[i] do
if self.components[i] is TEdit then
if Length(Trim(TEdit(frm.Components[i]).EditText))=0 then
begin
MessageBox(self.Handle, PChar('“'+TEdit(self.Components[i]).Hint+'”必须输入,不可以为空!'), PChar('提示'),MB_ICONINFORMATION or MB_OK);
TEdit(self.Components[i]).SetFocus;
exit;
end;
然后判断:var
i:integer;
for i:= 0 to self.ComponentCount-1 do
with self.components[i] do
if self.components[i] is TEdit then
if Length(Trim(TEdit(frm.Components[i]).EditText))=0 then
begin
MessageBox(self.Handle, PChar('“'+TEdit(self.Components[i]).Hint+'”必须输入,不可以为空!'), PChar('提示'),MB_ICONINFORMATION or MB_OK);
TEdit(self.Components[i]).SetFocus;
exit;
end;
先把select部分写好,where后面跟 1 = 1,
后面的条件都用 and + edit.text等等
if edit1<>'' then SQL:=SQL+' and 条件1= '+edit1.text;
if edit2<>'' then SQL:=SQL+' and 条件2= '+edit1.text;
if edit3<>'' then SQL:=SQL+' and 条件3= '+edit1.text;
可以任意组合任意多个条件,技巧就是先设置 select 语句中得where条件为真,然后把and加到后面得条件上去。