多条件数据库查询的优化方法
假设有一个名为employee的表，现在我们要从其中查询数据，条件有三个，由用户动态选择

Name=A
Sex=B
Age =C

其中条件A、B、C之间是与的关系，A、B、C均为动态选择，可以取其中的一个、两个或三个，也可以一个都不选，当三个条件都不选择时则认为是无条件查询

按照通常的做法：

这样，最终的结果有8个，即有8条查询语句，分别是

　　1.select * from employee;

　　2.select * from employee where Age =C ;

　　3.select * from employee where Sex=B;

　　4.select * from employee where Sex=B and Age=C;

　　5.select * from employee where Name=A;

　　6.select * from employee where Name=A and Age=C;

　　7.select * from employee where Name=A and Sex=B ;

　　8.select * from employee where Name=A and Sex=B and Age=C;

　　显然这是比较烦琐的，而且用到了多重嵌套IF语句，因而在条件增多时，其复杂程度将大大增加。对它进行优化，方法如下：
Dim Str_Result As String
Str_Result = ""
if A <> "" then
Str_Result="where Name =A"
end if
＇－－－－－－－－－－－－－－－－
if B <> "" then
if Str_Result="" then
Str_Result="where Sex=B"
else
Str_Result=Str_Result+"and Sex = B"
end if
end if
＇－－－－－－－－－－－－－－－－－
if C <> "" then
if Str_Result="" then
Str_Result="where Age =C"
else
Str_Result=Str_Result+"and Age=C"
end if
end if

　最终的结果查询语句为：　SQL = "select * from employee + Str_Result"

为什么运行后，出现 ＂Form 子句语法错误＂　提示



[ 本帖最后由 xzqsml 于 2010-1-29 10:17 编辑 ]

这样的东西在数据库中写个函数就搞定了,比在代码中用SQL好得多
给你贴个T-SQL的例子,你参照一下这种做法
--商品订单跟踪报表查询
create FUNCTION [dbo].[fn_MatOrderTacking]
    (
      @beginday DATETIME ,
      @endday DATETIME ,
      @doccode VARCHAR(100) ,
      @usertxt1 VARCHAR(100) ,
      @stcode VARCHAR(30) ,
      @matcode VARCHAR(100) ,
      @vndcode VARCHAR(100) ,
      @doctype VARCHAR(20)
    )
RETURNS @table TABLE
    (
      doccode VARCHAR(40) ,
      docdate DATETIME ,
      UserTxt1 VARCHAR(100) ,
      stcode VARCHAR(60) ,
      stname VARCHAR(100) ,
      vndcode VARCHAR(100) ,
      vndname VARCHAR(200) ,
      matcode VARCHAR(200) ,
      matname VARCHAR(100) ,
      uom VARCHAR(40) ,
      digit INT ,
      userdigit2 INT ,
      userdigit3 INT ,
      leftdigit INT ,
      hdmemo VARCHAR(500)
    )
AS
    BEGIN   
        IF @doctype = ''
            SET @doctype = '全部'   
        INSERT  INTO @table
                SELECT  a.doccode ,
                        c.postdate ,
                        a.usertxt1 ,
                        a.stcode ,
                        a.stname ,
                        a.vndcode ,
                        a.vndname ,
                        b.matcode ,
                        b.matname ,
                        b.uom ,
                        b.digit ,
                        b.userdigit2 ,
                        b.userdigit3 ,
                        CASE WHEN b.digit < 0 THEN 0
                             ELSE b.digit - b.userdigit3 - b.userdigit2
                        END ,
                        a.hdmemo
                FROM    imatdoc_h a
                        LEFT JOIN imatdoc_d b ON a.doccode = b.doccode
                        LEFT JOIN ppohd c ON a.usertxt1 = c.doccode
                WHERE   a.formid = 4998
                        AND c.formid = 405
                        AND ( @doccode = ''
                              OR a.doccode LIKE '%' + @doccode + '%'
                            )
                        AND ( c.postdate BETWEEN @beginday AND @endday )
                        AND ( @usertxt1 = ''
                              OR a.usertxt1 LIKE '%' + @usertxt1 + '%'
                            )
                        AND ( @stcode = ''
                              OR a.stcode LIKE '%' + @stcode + '%'
                            )
                        AND ( @vndcode = ''
                              OR a.vndcode LIKE '%' + @vndcode + '%'
                            )
                        AND ( @matcode = ''
                              OR b.matcode LIKE '%' + @matcode + '%'
                            )   
        IF @doctype = '仅入库'
            DELETE  FROM @table
            WHERE   digit <= 0   
        IF @doctype = '仅冲销'
            DELETE  FROM @table
            WHERE   digit >= 0   
        RETURN   
    END   
   
   
   

经过自己的测试，好像可行．．．请朋友们再验证下．．

＇－－－－－－－－－－－－－－－－－－
代码如下：

Dim Str_Result As String
Str_Result = ""　＇将变量为空

if A <> "" then
Str_Result = "Name =A"
end if
＇－－－－－－－－－－－－－－－－
if B <> "" then
if Str_Result = "" then
Str_Result = "Sex=B"
else
Str_Result = Str_Result + "and Sex = B"
end if
end if
＇－－－－－－－－－－－－－－－－－
if C <> "" then
if Str_Result = "" then
Str_Result = "Age = C"
else
Str_Result = Str_Result + "and Age=C"
end if
end if

最终结果：SQL = "select * from　employee　Where" + Str_Result
把Where 加在了SQL中，去除了If 中Where
Str_Result放在SQL引号外．．

[ 本帖最后由 xzqsml 于 2010-1-29 10:46 编辑 ]

SQL = "select * from　employee　"
Str_Result = ""
if a<>"" then
  Str_Result = Str_Result & "and Name=" & A
end if
if b<>"" then
  Str_Result = Str_Result & "and Sec=" & b
end if
if c<>"" then
  Str_Result = Str_Result & "and Age=" & c
end if
if str_result<>"" then
    sql=sql & " where " & mid(str_result,5)
end if

我没测试,你自己测试一下.

SQL = "select * from　employee where 1=1　"
if a<>"" then
  SQL  = SQL  & "and Name=" & A
end if
if b<>"" then
  SQL  = SQL  & "and Sec=" & b
end if
if c<>"" then
  SQL  = SQL  & "and Age=" & c
end if