我們知道，在一般的信息系統中，特別是主要信息表，如客戶基本信息，工單受理主界面，用戶會用到多條件組合查詢。
我看過一些系統，有的人是采用將一個表的欄位給用戶選擇，然後用戶可以針對某一欄位來下條件，但是這違背多條件組合查詢的需求初衷。多條件組合的需求是：用戶在不同的視覺情況下，可以使用某個條件或者忽略某個條件，而且前面講到的基於單表的按字段查詢也不適用於復雜的數據結構，比如設計的系統結合的表很多，則將用戶限制在單表的多字段是不行的。
我的做法是，作一個通用查詢條件窗口出來作為模板，其它界面使用時通過另存或者繼承，再修改為符合某個特定界面的條件查詢窗。而某些條件如果某窗口用不到，可以讓它enabled = false來解決。
我的查詢條件公用窗是類似這樣的。來自我的[鐵通客服自受理系統]:PB11
構建這個公用窗口的目的在於一勞永逸。但是要注意系統條件，常用的需要全部考慮在上面。
小提示：1. 對於不用的條件，enabled=false這樣可以讓用戶不能填寫，不發生雜亂難用的感覺。
2. 對於常用的下拉列表框內容條目，比如上面的工單類型和業務類型，顯然不要用"游標"從數據庫裡去fetch然後添加，因為這樣會造成一些掛起，嚴重時鎖表。(我最近看了一套自來水水管理系統，破解後看了看程序的event裡很多程序代碼，而且內嵌SQL很多。對於dropdownlist這樣的填充用的是游標fetch。我分析就是個新手寫的程序。客戶反映說經常鎖表。)其實這個例子應該是用一些辦法在程序啟動時，將一些FK表檢索到本地的全局緩沖裡，用的時候直接用，不需要每次都檢索。對數據庫應用來說，我遵循一個原則：盡量少地讀數據庫，充分考慮簡單高效，並杜絕重復動作。如果你使用pb那就可以將一些表整個檢索到datastore裡，然後對填充dropdownlist這樣的重復勞動，必然要寫好全局函數來處理，不需要傻傻地作重復編碼工作，或者將下拉框封裝成自定義控件，傳入dastore,用內部函數完成填充。
注: 如果多選鈕勾上，表明這個條件參加SQL查詢，否則不參加，那怎麼作到呢?技巧就是在SQL的條件裡構建用and連接的多組條件，通過邏輯表達式的一個控制參數，如果某個多選框沒選擇，則直接忽略它。
我的SQL語句是這樣的：
SELECT serv_100001.docno,
serv_100001.docdate,
serv_100001.createuser,
serv_100001.status
FROM serv_100001
WHERE (:control0 = 0 or
(:control0 = 1 and not serv_100001.status in(110,120,
130))) and
(:control1=0 or
(:control1=1 and serv_100001.docdate >= :date_begin and serv_100001.docdate <=:date_end) or
(:control1=2 and serv_100001.docno in(select docno100001 from serv_101001 where docdate >= :date_begin and docdate <=:date_end and status=20)) or
(:control1=3 and serv_100001.docno in(select docno100001 from serv_102001 where recdate >= :date_begin and recdate <=:date_end and status=20)) or
(:control1=4 and serv_100001.docno in(select docno100001 from serv_103001 where recdate >= :date_begin and recdate <=:date_end and status=20))) and
(:control3 = 0 or --單據類型
(:control3=1 and doctype = :doctype)) and
(:control4 = 0 or --業務種類
(:control4 = 1 and (servtype = :servtype or servtype2 = :servtype or servtype3 = :servtype or servtype4 = :servtype))) and
(0 in (:control5) or
(1 in (:control5) and serv_100001.status in(20)) or --新錄入
(2 in (:control5) and serv_100001.status in(40,60)) or --派單
(3 in (:control5) and serv_100001.status in(80)) or --簽收
(4 in (:control5) and serv_100001.status in(90,100,110)) or --繼續跟進
(5 in (:control5) and serv_100001.status in(70)) or --處理OK
(6 in (:control5) and serv_100001.status in(120,130,140))) and --回復客戶結案
(:control6 = 0 or
(:control6 = 1 and serv_100001.docno between :docno_begin and :docno_end)) and
(:control7 = 0 or
(:control7 = 1 and serv_100001.callnbr = :callnbr)) and
(:control8 = 0 or
(:control8 = 1 and serv_100001.duty = :dutydept)) and
(:control9 = 0 or
(:control9 = 1 and serv_100001.failnbr = :failnbraccount) or
(:control9 = 2 and serv_100001.adaccout = :failnbraccount)) and
(:control10 = 0 or
(:control10 = 1 and serv_100001.attn = :attn)) and
(:control11 = 0 or
(:control11=1 and (serv_100001.content like ''%''+ :content + ''%'' or serv_100001.remark like ''%''+ :content + ''%'')) or
(:control11=2 and serv_100001.docno in(select docno100001 from serv_101001 where advice like ''%''+ :content + ''%'' or designate like ''%''+ :content + ''%'' or reamrk like ''%''+ :content + ''%'')) or
(:control11=3 and serv_100001.docno in(select docno100001 from serv_102001 where content like ''%''+ :content + ''%'' or serv_100001.remark like ''%''+ :content + ''%'')) or
(:control11=4 and serv_100001.docno in(select docno100001 from serv_103001 where content like ''%''+ :content + ''%'' or serv_100001.remark like ''%''+ :content + ''%''))) and
(:control12 = 0 or
(:control12=1 and serv_100001.userid = :userid) or
(:control12=2 and serv_100001.docno in(select docno100001 from serv_101001 where userid = :userid)) or
(:control12=3 and serv_100001.docno in(select docno100001 from serv_102001 where userid = :userid)
) or
(:control12=4 and serv_100001.docno in(select docno100001 from serv_103001 where userid = :userid)))
這是我自己琢磨很久而想到的辦法。並且在兩套軟件中使用。效果還可以。
執行的最後SQL帶入條件參數後就是這樣的：
SELECT serv_100001.docno,
serv_100001.docdate,
serv_100001.createuser,
serv_100001.status
FROM serv_100001
WHERE (0 = 0 or
(0 = 1 and not serv_100001.status in(110,120,130))) and
(0=0 or
(0=1 and serv_100001.docdate >= ''1900-01-01 00:00:00.000'' and serv_100001.docdate <=''1900-01-01 00:00:00.000'') or
(0=2 and serv_100001.docno in(select docno100001 from serv_101001 where docdate >= ''1900-01-01 00:00:00.000'' and docdate <=''1900-01-01 00:00:00.000'' and status=20)) or
(0=3 and serv_100001.docno in(select docno100001 from serv_102001 where recdate >= ''1900-01-01 00:00:00.000'' and recdate <=''1900-01-01 00:00:00.000'' and status=20)) or
(0=4 and serv_100001.docno in(select docno100001 from serv_103001 where recdate >= ''1900-01-01 00:00:00.000'' and recdate <=''1900-01-01 00:00:00.000'' and status=20))) and
(0 = 0 or
(0=1 and doctype = 0)) and
(0 = 0 or
(0 = 1 and (servtype = 0 or servtype2 = 0 or servtype3 = 0 or servtype4 = 0))) and
(0 in (1,1,1,1,1,1) or
(1 in (1,1,1,1,1,1) and serv_100001.status in(20)) or
(2 in (1,1,1,1,1,1) and serv_100001.status in(40,60)) or
(3 in (1,1,1,1,1,1) and serv_100001.status in(80)) or
(4 in (1,1,1,1,1,1) and serv_100001.status in(90,100,110)) or
(5 in (1,1,1,1,1,1) and serv_100001.status in(70)) or
(6 in (1,1,1,1,1,1) and serv_100001.status in(120,130,140))) and
(0 = 0 or
(0 = 1 and serv_100001.docno between '''' and '''')) and
(0 = 0 or
(0 = 1 and serv_100001.callnbr = '''')) and (0 = 0 or
(0 = 1 and serv_100001.duty = 0)) and
(0 = 0 or
(0 = 1 and serv_100001.failnbr = '''') or
(0 = 2 and serv_100001.adaccout = '''')) and
(0 = 0 or
(0 = 1 and serv_100001.attn = '''')) and
(1 = 0 or
(1=1 and (serv_100001.content like ''%''+ ''接通率'' + ''%'' or serv_100001.remark like ''%''+ ''接通率'' + ''%'')) or
(1=2 and serv_100001.docno in(select docno100001 from serv_101001 where advice like ''%''+ ''接通率'' + ''%'' or designate like ''%''+ ''接通率'' + ''%'' or reamrk like ''%''+ ''接通率'' + ''%'')) or
(1=3 and serv_100001.docno in(select docno100001 from serv_102001 where content like ''%''+ ''接通率'' + ''%'' or serv_100001.remark like ''%''+ ''接通率'' + ''%'')) or
(1=4 and serv_100001.docno in(select docno100001 from serv_103001 where content like ''%''+ ''接通率'' + ''%'' or serv_100001.remark like ''%''+ ''接通率'' + ''%''))) and
(0 = 0 or
(0=1 and serv_100001.userid = '''') or
(0=2 and serv_100001.docno in(select docno100001 from serv_101001 where userid = '''')) or
(0=3 and serv_100001.docno in(select docno100001 from serv_102001 where userid = '''')) or
(0=4 and serv_100001.docno in(select docno100001 from serv_103001 where userid = '''')))
我們很容易看明白。就是其中的controlx起到了很大作用，從而使得構建復雜的成組的條件容易實現，而且不影響到執行效率，因為邏輯關系式遵循短路原則。如果某組條件用戶沒勾選使用則control1=0就滿足，從而不會執行後面的語句，整個分組條件就滿足了，所以語句本身不影響速度。
*另外在網上有文章[使用Instr()與decode()進行多條件組合查詢]介紹Oracle下的函數，其實mssql用substring可以傳入一個字符串作為參數，從而解決上面我的代碼中的controlx太多的缺點。就是類似於control_string=“YYYYNNYY”,則substring(control_string,1,1)=''Y''代表某個條件進行了勾選。當然還有charindex()函數可以用來傳條件，比如多個條件傳入的參數是：control_string=“A_CD_FGH”則代表A,C,D,F,G,H組的條件被勾選。用charindex(''A'',control_string)>0則可以判斷A組條件被選擇了。
*當然，如果你在pb中的話，數據框檢索條件裡是可以用control[10]這樣的數組傳入的，則SQL的where條件可以這樣寫: (1 in (:control_array) and id = :id)。每組條件用不同的編號。比如control_arrar={1,0,0,0,5,9}，id=5傳入後就是： (1 in (1,0,0,0,5,9) and id = 5),而且用這種方式傳遞比字符串傳入速度更快。
*以上所說方法有一個前提就是控制參數必須在前面。利用邏輯運算的短路原則。用反了可就問題大了。
*另外ASP的人說用sql_string="......." + "........."來拼湊代碼，也是一個方法。但傳遞到服務器端的代碼跟蹤出來，可讀性就非常差了。而且把檢索看作一個接口的話，這個接口是不固定的，造成代碼和數據邏輯的混雜不清。
如果大家有更好的辦法，歡迎賜教。
