sql查询语句的拼凑小技巧（高手勿喷）_SQL

sql查询语句的拼接小技巧（高手勿喷）

1. 基本的查询语句后面加上 WHERE 1=1，便于增加查询条件。

ASkStr := 'select * from Twork where 1=1 ';if length(cxTEworkid.Text) <> 0 then beginASkStr := ASkStr + ' and GWORKID like ' +quotedstr('%' + cxTEworkid.Text + '%')end;

2. 多表查询可以用一个字符串代替一个表。逻辑上更清晰。

WorkTable = 'select GWorkId from TWork where 1=1 ';PayTable = 'select sum(GXMtotal) as SGXMtotal,GXMType from TPAY where GWorkId In ';PayGroupBy = ' Group By GXMType ';

ASkStr := PayTable + ' ( ' + WorkTable + tmpwhere + ' ) ' + PayGroupBy;

3.自己定义一个连接函数，判断数据库初始化是否成功，便于控制。

function Tdm.InitDbOk: boolean;begin  result := true;  if fileexists(DBFileName) then  begin    FDConn.DriverName := 'SQLite';    FDConn.LoginPrompt := false;    FDConn.Params.Clear;    FDConn.Params.Values['Database'] := DBFileName;    FDConn.Params.Values['DriverID'] := 'SQLite';    FDConn.Params.Values['CharacterSet'] := 'utf8';    try      FDConn.Connected := true;    except      result := false;    end;  end  else    result := false;end;