???前言：
??????這段時間還一直在努力之中，這一次在SQL的構建上遇到一個挑戰，不過這一次的挑戰是自找的。

??????開發環境是VB6的DLL＋ASP，分到的活是一個報表模塊。
??????需求
?????????我這一塊的報表，現階段是要實現兩個功能(就我看應該只是一個，需求理解錯誤可不是我的事兒)，為了與其它的模塊保持一致，工作流程如下：
?????????1.輸入查詢的年 / 月;
?????????2.查詢結果並以網頁表格的方式提供預覽;
?????????3.選擇發送郵件功能;
?????????4.選取需要發送的郵件收件人列表;
?????????5.查詢結果生成Excel文件並以郵件附件的方式發送。
?????? 輸入
?????????參數是一個 年份值＋月份值：
?????????1. lngYear (0001-9999)，年份值;
?????????2. lngMonth (1-12)，月份值.
?????????由於Input參數少，JS檢驗比較簡單，因此會在客戶端JS提供第一層的校驗，而模塊裡面就不再做檢驗，捕獲異常即可。
??????輸出
?????????根據輸入的參數，搜尋記錄，提供瀏覽以及發送Excel報表的能力(以郵件附件的方式發送，在此不做論術)。

?????????再來說說環境的事情，開發環境是用VB的DLL實現功能封裝，因此我的報表會是一個cls，ASP頁面只要實現View即可。數據環境是用MSSQL2000，第1個難點出現在這裡：數據環境雖說是用的MSSQL，但是卻不能使用Procedure;DB的設計也增加了難度：整個DB實現上是包含了兩個系統的數據庫，不同的結構，不同的命名規則，卻有相同含義的字段。為了便於說明，拉一個示例數據庫出來先：

????????? 見 PDM 圖 (去掉全部無關字段)

?????????可以看到，DB中以 tbXXX 方式命名的表與 XXX(AS400) 的表有著很大的差異，但是它們之間又存在著相同之處。
??????再來看看表間的隱式關聯關係 (沒有以FK的方式加以約束)

• 合約租金(tbContractRent)? 與 職場(tbPlace)的關聯：職場代碼相同 ，合約是職場在某一段時間的表現，即相同職場在某一時刻只會存在一份合約
• 合約租金(tbContractRent)? 與 職場單位(tbUnit)的關聯：單位的坪數生效日/坪數失效日的區間 落在一份合約租金生效/租金失效區間之內
  
• 職場單位(tbUnit) 與 單位資料AS400(AAABREP)的關聯：單位代碼 + 單位序號 相同
  
• 職場單位(tbUnit) 與 單位資料2AS400(DAD6CPP)的關聯：(單位代碼 + 單位序號 相同) 並且 (原職場代碼 = 職場代碼 或 新職場代碼 = 職場代碼)
• 合約租金(tbContractRent)? 與 職場資料AS400(DAC9CPP)的關聯：職場資料到期日?落在 一份合約租金生效/租金失效日區間之內

???????功能描述：

1. “閒置租約”：
   職場單位與租約相關，這裡的“閒置”狀態由單位引發，描述是指在一份合約內，單位發生過“撤銷”，然後就會從職場中“遷出”，那麼遷出日期－－合約租金失效日的區間內即是“閒置”。
   因此該功能的條件入口表是 合約租金(tbContractRent)，該功能的業務邏輯比較複雜，流程如下：
   a. 在考察期內有效的合約;
   b. 在合約中的單位發生過“撤銷”-- 通過(tbUnit)連接到(AAABREP)檢查是否存在有撤銷日期(合法日期 並且 發生在考察期之前)，另外由於存在業務邏輯，這裡的撤銷日期在使用時需要向後加一個月;
   c. 如果該單位符合撤銷條件則通過(tbUnit)連接到(DAD6CPP)獲取搬遷日期，同樣的，這裡所得到的搬遷日期由於一定的業務邏輯，還需要檢查它的有效性，只有大於撤銷日期的才會認定為是合法的搬遷日期，否則將使用(tbUnit)表中的坪數失效日來做為該單位的搬遷日期;
   d. 搬遷日期必須早於考察的結束時間;
2. “待退職場”：
   是指一個職場的最後一份合約的失效日落在考察期內，由於相同資料的以 XXXAS400表為主，因此條件入口表是職場資料AS400(DAC9CPP)
3. 最終的表現形式是一張2維表格的方式，即功能1 與 功能2的結果要放在一起

??????約束：
?????????不允許使用存儲過程，只能在VB的DLL中實現。

??????思考及方案選擇：
?????????1. 功能1是這次任務的難點，實現邏輯需要跑的表比較多，而且邏輯間也比較複雜，看上去並不能簡單的用 Inner/Left Join + Where就可以搞定那幾表。
?????????2. 最終的結果集會建立在指定的幾張表上，與實現 功能 所聯接的表並不相同，無法在結果表上進行直接篩選，還需要將上面所拿到的條件結果集與最終的表現表進行一次關聯。
?????????3. 實現這次功能的方法有兩種：
?????????3.1. 用ADO對象的嵌套來實現，配合上帶層次的 Function ，這是比較傳統的解決方案，完全可以解決這類問題。
????????????優點
????????????a. 適用面廣，複雜度比較低;
????????????b. 函數封裝，功能邏輯清晰;
????????????缺點
????????????a.?很明顯的，在這次的任務中要實現功能，需要使用3層以上的循環，這種邊循環邊查詢的方式的效率是極低的;
?????????3.2. 嚐試使用複雜SQL來構造邏輯實現。
????????????使用這種解決方法，在該問題上算是一種比較酷的解決。使用它的好處就是能提高查詢效率，當然對於我來說，這算是一種嚐試和創新，嘻嘻。
????????????優點
????????????a. 查詢效率高，因為它只會發出一條SQL;
????????????缺點
????????????a. 難於構建，複雜度高;
????????????b. 如果結構不好則很難調試及更改，發生需求變更時改動難度大;

??????細節分析：
?????????最終俺決定使用複雜SQL來構建邏輯，雖說實現起來會很困難，時間也蠻緊巴的，不過這是一個挑戰，我之前也對自己的能力抱有信心。好了，切入正題，談談我的實現細節。
?????????對於之前所考慮到的問題，我針對性的設計一種結構來應付它。
?????????a. 由於這次的查詢條件表比較多，各表間無法直連，所以我在表這一層上使用封裝，以降低它們之間的瓜葛，並試圖將表這一層的變動壓抑在封裝之內，這一點至關重要，因為SQL就象是一座塔，底部會由幾個基礎表支撐，這就好比萬噸壓力全都壓在幾個支撐柱上，一旦柱子垮了塔也將不復存在;
?????????b. 在 a 的表封裝基礎上使用模塊化的構建，SQL語句說到底都是一句句拼湊起來的，再複雜的語句也不過如此。因此按照邏輯一步步的走下來，逐步的封成單一的模塊，最後再象積木一樣搭建起來是很重要的;
?????????c. 這次頁面的功能是有分成 2 個部分的，而這2功能的查詢入口表及連接順序又不相同(不要跟我說表 A 連接到 表 B 與表 B 連接到表 A 的邏輯是相同的)，不過邏輯的核心表都落在合約租金(tbContractRent)?上，因此采取將產出表與條件表切割開來，而2功能使用聯合的辦法來完成產出與邏輯的分離，SQL的結構示例成為這樣： Select * From 產出表 Join (功能1 Union 功能2) As 功能表?On 產出與邏輯間的關聯。
?????????d. 對於SQL的調試問題，由於成品SQL的體積會使得調試起來頗為複雜，因此我在功能模塊上增加調試用的接口，這樣就能夠將逐步形成的半成品給輸出來，有利於外步調試時的分步分析;
?????????e. 為便於SQL調試，在代碼的書寫上使用了一點小技巧。通常在合成SQL時語句的寫法會是這樣:
?????????strSQL="Select * From Table Where col1='" & p1 & "' And col2='" & p2 & "'"
?????????這樣的寫法會增加源代碼的檢查難度，大量的字符串連接符和變量充斥其中，現在改成這樣寫:
?????????strSQL = "Select * From Table Where col1=@p1 And col2=@p2"
?????????在 SQL 語句搭建完成後再使用 strSQL = Repalce(strSQL,"@p1","'xxx'") 的方式來代入參數，即不會影響使用，又降低了源碼的檢查難度，而且配合調試時輸出:
?????????declare @p1 char(8),@p2 char(8)
?????????select @p1='xxx',@p2='yyy'
?????????這樣一來，就可以很方便地將調試 SQL 語句直接 Copy 進查詢分析器，直接修改輸入參數進行調試分析了。

??????實現代碼：
?????????第一次嚐試使用這種方法來實現，花了很長時間(約2天時間)才完成，還好調試時只遇到了一個很小的失誤，以後模塊又經歷了多次變更，後續文章中會加以討論在這種實現方式下我是如何跟隨需求變更的，當然，經過了N次的變更之後，還是....

CODE
' '**************************************************************************************************
' *程式功能??:???????獲取"待退（閒置）租約資料報表"所需記錄
' *開發人員??:　?????ddm?2006/12/17
' *異動人員??:
' *傳出值????:???????rsPlace?????????--查出的主記錄集(包含職場/單位)
' *傳出值????:???????rsOwner?????????--查出的次記錄集(包含房東資料)
' *傳入值????:???????lngYear?????????--考察年份
' *傳入值????:???????lngMonth????????--考察月份
' *回傳值????:???????boolean?成功=true;失敗=false
' '**************************************************************************************************
Public ? Function ?fGetFreePlace(ByRef?rsPlace? As ?Variant,?ByRef?rsOwner? As ?Variant,?ByVal?lngYear? As ?Variant,?ByVal?lngMonth? As ?Variant)? As ? Boolean
On ? Error ? GoTo ?ErrHandler
???? Dim ?strYear? As ? String ,?strMonth? As ? String
????strYear? = ? Trim ( "" ? & ?lngYear)
????strMonth? = ? Trim ( "" ? & ?lngMonth)
???? If ? IsDate (strYear? & ? " - " ? & ?strMonth)? = ? False ? Then
???????? ' 輸入參數檢查
????????fGetFreePlace? = ? False
???????? GoTo ?ErrHandler
???????? Exit ? Function
???? End ? If
????
???? Dim ?strSql? As ? String ,?strWhere? As ? String
????
???? ' ***************閒置開始***************
????strSql? = ? ""
????
???? ' 符合條件的?"閒置"?租約--(有效租約的相關單位在考察期間發生了?"整編")?&&?("迕出日期")?不能在考察期間以後(參照?clsMonthRent.fFreeRent?計算閑置租金)
???? ' 準備SQL的連接表
???? Dim ?strContract_TB? As ? String ,?strUnit_TB? As ? String ,?strAAABREP_TB? As ? String ,?strDAD6CPP_TB? As ? String
???? ' ???tbContractRent合約租金表
???? ' ???????邏輯1:未刪除的
???? ' ???????邏輯2:租金生效日--租金失效日?的日期范圍(合約生效范圍)大於　考察期的范圍
????strContract_TB? = ? " (?SELECT?*?from?tbContractRent?where?tbContractRent.CDelFlag='N'?and?(tbContractRent.CStartDate?<?'@StartDate8'?and?'@EndDate8'?<?tbContractRent.CEndDate)?)?tbContractRent "
???? ' ???tbUnit職場單位表
????strUnit_TB? = ? " (Select?*?From?tbUnit?Where?CDelFlag='N')?tbUnit "
???? ' ???AAABREP單位資料(AS400)表
???? ' ???????邏輯1:撤銷日期?早於?考察期
???? ' ???????注意:撤銷日期(整編日期)會是實際整編日期的前一個月，因此必須先加1月以得到實際的整編日期
???? ' ???????????取出表中的?PK:(ABABCD,ABI2CD)
???? ' ???????????合成實際整編日期列
????strAAABREP_TB? = ? " Select?ABABCD,ABI2CD,Case?right(ABACDT,2)?when?'12'?then?CAST((CAST(LEFT(ABACDT,4)?as?int)+1)?as?char(4))?+?'01'?else?LEFT(ABACDT,4)?+?RIGHT('0'+CAST((CAST(right(ABACDT,2)?as?int)+1)?as?varchar(2)),2)?End?As?ABACDT?From?AAABREP "
???? ' ???????????整編月份?<?考察月份
????strAAABREP_TB? = ? " Select?*?from?( " ? & ?strAAABREP_TB? & ? " )?AAABREP?Where?ABACDT?<?'@Date6' "
???? ' ???????????包裝
????strAAABREP_TB? = ? " ( " ? & ?strAAABREP_TB? & ? " )?AAABREP "
???? ' ???DAD6CPP單位資料檔2(AS400)表
???? ' ???????邏輯1:由於在該表?中?當單位發生"整編時"?並不一定?會記錄下?"搬遷日期(參看clsMonthRent.fFreeRent中的邏輯)"
???? ' ???????????判斷邏輯1是否成立的條件:?DAD6CPP單位資料檔2(AS400)表中的"搬遷日期"?必須晚於?AAABREP單位資料(AS400)表中的"整編日期"
???? ' ???????????處理:分步連接,使用?Left/Right?Join,?與前3表的結果表進行連接
????strDAD6CPP_TB? = ? " DAD6CPP "
????
???? ' 準備表間連接條件
????strWhere? = ? " ?Where "
???? ' ???tbContractRent合約租金表?與?tbUnit職場單位表
???? ' ???????聯合邏輯:(職場ID相同)?&&?(職場單位"坪數生效日","坪數失效日"區間要在?合約租金"租金生效","租金失效"區間之內)
????strWhere? = ?strWhere? & ? " ?(tbContractRent.CPlaceCode?=?tbUnit.CPlaceCode)?and?((tbContractRent.CStartDate?>=?tbUnit.CAreaBegin)?and?(tbUnit.CAreaEnd?<=?tbContractRent.CEndDate)) "
???? ' ???tbUnit職場單位表?與?AAABREP單位資料(AS400)表
???? ' ???????聯合邏輯:(單位代碼相同)?&&?(單位序號相同)
????strWhere? = ?strWhere? & ? " ?and?(?(tbUnit.CUnitID?=?AAABREP.ABABCD)?and?(tbUnit.CUnitSeq?=?AAABREP.ABI2CD)?) "
????
???? ' 連接
???? ' ???步驟一:連接1.合約租金表;2.職場單位表;3.單位資料(AS400)表,並應用3表的篩選條件
???? ' ???????取出合約租金表中的?PK:(CContractid);
???? ' ???????取出單位表中的?PK:(CPlaceCode,CUnitCode,CUnitName,CAreaBegin);
???? ' ???????取出單位表中的?(CAreaEnd);
???? ' ???????取出單位表中的?FK(CUnitID,CUnitSeq);
???? ' ???????AAABREP單位資料(AS400)表中的?(ABACDT).
????strSql? = ? " Select?tbContractRent.CContractid?as?CContractid,tbUnit.CPlaceCode?as?CPlaceCode,tbUnit.CUnitCode?as?CUnitCode,tbUnit.CUnitName?as?CUnitName,tbUnit.CAreaBegin?as?CAreaBegin,tbUnit.CAreaEnd?as?CAreaEnd,tbUnit.CUnitID?as?CUnitID,tbUnit.CUnitSeq?as?CUnitSeq,AAABREP.ABACDT?as?ABACDT?from? "
????strSql? = ?strSql? & ?strContract_TB? & ? " , " ? & ?strUnit_TB? & ? " , " ? & ?strAAABREP_TB
????strSql? = ?strSql? & ?strWhere
???? ' ???步驟二:連接1.步驟一的結果集
???? ' ???????聯合邏輯:(原職場代號?||?新職場代號?==?單位職場代號)?&&?(單位代碼相同)?&&?(單位序號相同)?&&?(搬遷聯合日期?晚於?單位撤銷日期)
???? ' ???????取出結果集1中的?全部字段(*);
???? ' ???????取出單位資料檔2(AS400)表中的?(D6AOD8).
???? ' ???????????如果單位資料檔2(AS400)表中無合法的?"遷出日期"?則使用?"單位坪數失效日期"
????strSql? = ? " Select?tbResult1.*,ISNULL(DAD6CPP.D6AOD8,tbResult1.CAreaEnd)?as?D6AOD8?From?( " ? & ?strSql? & ? " )?tbResult1?Left?Join? " ? & ?strDAD6CPP_TB
????strSql? = ?strSql? & ? " ?On?(tbResult1.CPlaceCode?=?DAD6CPP.D6LQCD?Or?tbResult1.CPlaceCode?=?DAD6CPP.D6LSCD) "
????strSql? = ?strSql? & ? " ?and?(tbResult1.CUnitID?=?DAD6CPP.D6FHCD) "
????strSql? = ?strSql? & ? " ?and?(tbResult1.CUnitSeq?=?DAD6CPP.D6FICD) "
???? ' ???????撤銷日期是一個6位日期只會考察到月,搬遷日期是一個8位日期,因此必須按6位的月份來計算
????strSql? = ?strSql? & ? " ?and?(tbResult1.ABACDT?<=?LEFT(DAD6CPP.D6AOD8,6)) "
???? ' ???步驟三:對步驟二的結果集進行篩選
???? ' ???????邏輯:單位的搬遷日期必須早於?考察期的結止日期
???? ' ???????這裡合成?"閒置"?的最後記錄集
???? ' ???????取1.合約租金表中的?PK:(CContractid)
???? ' ???????取2.用於連接職場的?PK:(CPlaceCode)
???? ' ???????取3.單位表?PK:(CPlaceCode,CUnitCode,CUnitName,CAreaBegin)
????strSql? = ? " Select?CContractid,CPlaceCode,CUnitCode,CUnitName,CAreaBegin?From?( " ? & ?strSql? & ? " )?tbResult2 "
????strSql? = ?strSql? & ? " ?Where?D6AOD8?<?'@EndDate8' "
???? ' ???閒置邏輯完成:
???? ' ???????記錄條件SQL,用於外部調試
????m_strFreeSql? = ?strSql
???? ' ***************閒置結束***************
????
???? ' ***************待退開始***************
???? Dim ?strDAC9CPP_TB? As ? String ,?strSql1? As ? String ,?strWhere1? As ? String
???? ' ???待退邏輯:最新一筆?"職場(AS400)"的到期日落在考察期內;例如考察期為95年12月,職場A有兩條:a.95.1-95.12;b.96.1-96.6;這不算待退.
???? ' ???步驟一:
???? ' ???????準備職場資料記錄檔(AS400)表,每條職場只取最新的記錄
????strDAC9CPP_TB? = ? " Select?DAC9CPP2.*?From "
????strDAC9CPP_TB? = ?strDAC9CPP_TB? & ? " ?(select?C9K3CD,max(C9JZNB)?C9JZNB?from?DAC9CPP?group?by?C9K3CD)?DAC9CPP1?join?DAC9CPP?DAC9CPP2 "
????strDAC9CPP_TB? = ?strDAC9CPP_TB? & ? " ?on?DAC9CPP1.C9K3CD?=?DAC9CPP2.C9K3CD?and?DAC9CPP1.C9JZNB?=?DAC9CPP2.C9JZNB "
????strDAC9CPP_TB? = ? " ( " ? & ?strDAC9CPP_TB? & ? " )?DAC9CPP "
???? ' ???????篩選職場資料記錄檔(AS400)表,到期日落在考察期內
???? ' ???????取出表中的PK:(C9K3CD,C9JZNB)
???? ' ???????取出表中的退租日
????strSql1? = ? " Select?C9K3CD,C9JZNB,C9AGD8 "
????strSql1? = ?strSql1? & ? " ?From? " ? & ?strDAC9CPP_TB
????strSql1? = ?strSql1? & ? " ?Where?C9AGD8?Between?@StartDate8?And?@EndDate8 "
???? ' ???步驟二:
???? ' ???????通過符合條件的職場資料(AS400),與合約租金表關聯
???? ' ???????包裝
????strSql1? = ? " ( " ? & ?strSql1? & ? " )?DAC9CPP "
???? ' ???????連接合約租金表
???? ' ???????????關聯邏輯:(職場ID相同)?&&?(職場的到期日落在合約租金表的"租金生效日","租金失效日"區間內(如果能夠確認,應該職場的到期日=合約的租金失效日))
????strSql1? = ?strSql1? & ? " , " ? & ? " (Select?*?From?tbContractRent?Where?CDelFlag='N')?tbContractRent "
????strWhere1? = ? " ?Where?(DAC9CPP.C9K3CD?=?tbContractRent.CPlaceCode)?And?(DAC9CPP.C9AGD8?Between?tbContractRent.CStartDate?And?tbContractRent.CEndDate) "
????
???? ' ???????連接單位表
???? ' ???????????關聯邏輯:(職場ID相同)?&&?(職場單位"坪數生效日","坪數失效日"區間要在?合約租金"租金生效","租金失效"區間之內)
????strUnit_TB? = ? " (Select?*?From?tbUnit?Where?CDelFlag='N')?tbUnit "
????strSql1? = ?strSql1? & ? " , " ? & ?strUnit_TB
????strWhere1? = ?strWhere1? & ? " ?And?(tbContractRent.CPlaceCode?=?tbUnit.CPlaceCode)?And?((tbContractRent.CStartDate?>=?tbUnit.CAreaBegin)?and?(tbUnit.CAreaEnd?<=?tbContractRent.CEndDate)) "
???? ' ???步驟三:
???? ' ???????將連接的3表封裝,合成?"待退"?的最後記錄集
???? ' ???????取1.合約租金表中的?PK:(CContractid)
???? ' ???????取2.用於連接職場的?PK:(CPlaceCode)
???? ' ???????取3.單位表?PK:(CPlaceCode,CUnitCode,CUnitName,CAreaBegin)
????strSql1? = ? " Select?tbContractRent.CContractid?as?CContractid,tbUnit.CPlaceCode?as?CPlaceCode,tbUnit.CUnitCode?as?CUnitCode,tbUnit.CUnitName,tbUnit.CAreaBegin?From? " ? & ?strSql1
????strSql1? = ?strSql1? & ?strWhere1
???? ' ???待退邏輯完成:
???? ' ???????記錄條件SQL,用於外部調試
????m_strEndSql? = ?strSql1
???? ' ***************待退結束***************
????
???? ' ***************閒置?UNION?待退***************
???? Dim ?strUnionSql? As ? String ,?strUnionSql1? As ? String ,?strPlace_TB? As ? String ,?strFinalUnion_TB? As ? String
???? ' ???準備最終結果集表(閒置?UNION?待退)
????strFinalUnion_TB? = ? " ( " ? & ?strSql? & ? " ?Union? " ? & ?strSql1? & ? " )?tbFinalUnion "
???? ' ???準備主記錄集表頭所要的表
????strDAC9CPP_TB? = ?strDAC9CPP_TB??? ' 使用上面的邏輯,取AS400相同職場資料"DAC9CPP"中最新的一條
????strContract_TB? = ? " (Select?*?From?tbContractRent?Where?CDelFlag='N')?tbContractRent "
????strPlace_TB? = ? " (Select?*?From?tbPlace?Where?CDelFlag='N')?tbPlace "
????strUnit_TB? = ? " (Select?*?From?tbUnit?Where?CDelFlag='N')?tbUnit "
????
???? ' 報表需求:
???? ' ???????根據:"租賃系統補充需求_20061130.doc"中?租賃系統補充需求/第5點?:
???? ' ???單位中文名稱．職場代碼．地址．虛坪．每坪租金單價．租金．租金福利預算．當月應代扣租額．停車位租金．停車位數量．
???? ' ???押金．起租日．續租日．到期日．搬遷訊息．提前解約規定及罰則．復原條件．空調維護保養責任．租約附註事項說明．房東．
???? ' ???聯絡人．聯絡人電話．租金給付方式．大樓名稱．管委會聯絡人．管委會聯人電話
???? ' ???退租日
???? ' ???????列特殊邏輯:
???? ' ???????????1.退租日為合約終止日期或提前退租日
???? ' ???????????2.當月應代扣租額：若租金＜租金福利預算?則為"0"，反之，則為:租金－租金福利預算
????
???? ' 拼裝返回結果記錄集SQL:
???? ' ???由於房東的資料只能關聯到?"房東資料上"?,?采取雙結果記錄集的方式返回
???? ' ???步驟一:
???? ' ???????拼主記錄集表頭
???? ' 單位中文名稱(單位序號可能為空,IF?==?空?Then?get單位中文名稱)
????strUnionSql? = ? " Select?Case?LEN(LTRIM(RTRIM(ISNULL(tbUnit.CUnitCode,''))))?When?0?Then?tbUnit.CUnitName?Else?tbUnit.CUnitCode?End?As?CUnitName "
???? ' 職場代碼
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9K3CD?As?CPlaceCode "
???? ' 地址
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9BAIG?AS?CAddress "
???? ' 虛坪
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9JCNB?AS?CTotalArea "
???? ' 每坪租金單價
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9AVVA?AS?CPrice "
???? ' 租金
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9AUVA?AS?CRent "
???? ' 租金福利預算
????strUnionSql? = ?strUnionSql? & ? " ,tbPlace.CRentBudget?AS?CRentBudget "
???? ' 當月應代扣租額
????strUnionSql? = ?strUnionSql? & ? " ,Case?When?Cast(IsNull(DAC9CPP.C9AUVA,'0.00')?As?Money)?<?Cast(IsNull(tbPlace.CRentBudget,'0.00')?As?Money)?Then?'0.00' "
????strUnionSql? = ?strUnionSql? & ? " ???Else?Cast(Cast(IsNull(DAC9CPP.C9AUVA,'0.00')?As?Money)?-?Cast(IsNull(tbPlace.CRentBudget,'0.00')?As?Money)?As?char(8)) "
????strUnionSql? = ?strUnionSql? & ? " ?End?AS?CDkRent "
???? ' 停車位租金
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9AXVA?AS?CPartRent "
???? ' 停車位數量
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9JENB?AS?CPartCount "
???? ' 押金
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9BAVA?AS?CForegift "
???? ' 起租日
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9AED8?AS?CStartDate "
???? ' 續租日
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9AFD8?AS?CRestartDate "
???? ' 到期日
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9AGD8?AS?CEndDate "
???? ' 搬遷訊息
????strUnionSql? = ?strUnionSql? & ? " ,tbPlace.CMoveInfo?AS?CMoveInfo "
???? ' 提前解約規定及罰則
????strUnionSql? = ?strUnionSql? & ? " ,tbPlace.CRules?AS?CRules "
???? ' 復原條件
????strUnionSql? = ?strUnionSql? & ? " ,tbPlace.CRecover?AS?CRecover "
???? ' 空調維護保養責任
????strUnionSql? = ?strUnionSql? & ? " ,tbPlace.CAirCondition?AS?CAirCondition "
???? ' 租約附註事項說明
????strUnionSql? = ?strUnionSql? & ? " ,tbPlace.CAppendRule?AS?CAppendRule "
???? ' 大樓名稱
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9DCIG?AS?CBuildingName "
???? ' 管委會聯絡人
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9DDIG?AS?CManager "
???? ' 管委會聯人電話
????strUnionSql? = ?strUnionSql? & ? " ,DAC9CPP.C9LXCD?AS?CManagerTel "
???? ' 退租日tbContractRent
????strUnionSql? = ?strUnionSql? & ? " ,Case?LEN(LTRIM(RTRIM(IsNull(tbContractRent.CAdvanceBackRentDate,'')))) "
????strUnionSql? = ?strUnionSql? & ? " ???When?8?Then?tbContractRent.CAdvanceBackRentDate "
????strUnionSql? = ?strUnionSql? & ? " ???Else?tbContractRent.CEndDate "
????strUnionSql? = ?strUnionSql? & ? " ?End?As?CBackRent "
????
???? ' ???步驟二:
???? ' ???????表頭掛接?From,主記錄集需要?From?的表:DAC9CPP/tbContractRent/tbPlace/tbUnit/tbFinalUnion最終結果集表(閒置?Union?待退)
????strUnionSql? = ?strUnionSql? & ? " ?From?((( " ? & ?strFinalUnion_TB
????strUnionSql? = ?strUnionSql? & ? " ?Join? " ? & ?strDAC9CPP_TB? & ? " ?On?tbFinalUnion.CPlaceCode=DAC9CPP.C9K3CD) "
????strUnionSql? = ?strUnionSql? & ? " ?Join? " ? & ?strPlace_TB? & ? " ?On?tbFinalUnion.CPlaceCode=tbPlace.CPlaceCode) "
????strUnionSql? = ?strUnionSql? & ? " ?Join? " ? & ?strContract_TB? & ? " ?On?tbFinalUnion.CContractid=tbContractRent.CContractid) "
????strUnionSql? = ?strUnionSql? & ? " ?Join? " ? & ?strUnit_TB? & ? " ?On?tbFinalUnion.CPlaceCode=tbUnit.CPlaceCode "
????strUnionSql? = ?strUnionSql? & ? " ???And?tbFinalUnion.CUnitCode=tbUnit.CUnitCode "
????strUnionSql? = ?strUnionSql? & ? " ???And?tbFinalUnion.CUnitName=tbUnit.CUnitName "
????strUnionSql? = ?strUnionSql? & ? " ???And?tbFinalUnion.CAreaBegin=tbUnit.CAreaBegin "
????
???? ' ???步驟三:
???? ' ???????按?CPlaceCode?Asc,CUnitName?排序
????strUnionSql? = ?strUnionSql? & ? " ?Order?By?CPlaceCode?Asc,CUnitName "
????
????
???? ' 房東資料需要關聯的表:合約租金表/合約租金房東資料/房東資料(tbOwner)/付款方式(tbPayMode)
???? Dim ?strContractOwner_TB? As ? String ,?strOwner_TB? As ? String ,?strPayMode_TB? As ? String
????strContractOwner_TB? = ? " (Select?*?From?tbContractOwner?Where?CDelFlag='N')?tbContractOwner "
????strOwner_TB? = ? " tbOwner " ? ' (Select?*?From?tbOwner?Where?CDelFlag='N')
????strPayMode_TB? = ? " tbPayMode " ? ' (Select?*?From?tbPayMode?Where?CDelFlag='N')
????
???? ' ???步驟一:
???? ' ???????拼房東資料表頭:房東．聯絡人．聯絡人電話．租金給付方式
???? ' 用於外部連接的職場ID
????strUnionSql1? = ? " Select?tbFinalUnion.CPlaceCode?As?CPlaceCode "
???? ' 房東
????strUnionSql1? = ?strUnionSql1? & ? " ,tbOwner.COwner?AS?COwner "
???? ' 聯絡人
????strUnionSql1? = ?strUnionSql1? & ? " ,tbOwner.CLinkman?AS?CLinkman "
???? ' 聯絡人電話
????strUnionSql1? = ?strUnionSql1? & ? " ,tbOwner.CLinkTel?AS?CLinkmanTel "
???? ' 租金給付方式
????strUnionSql1? = ?strUnionSql1? & ? " ,tbPayMode.CModeName?AS?CRentPayType "
????
???? ' ???步驟二:
???? ' ???????表頭掛接?From,房東記錄集需要?From?的表:tbContractOwner/tbOwner/tbPayMode/tbFinalUnion最終結果集表(閒置?Union?待退)
????strUnionSql1? = ?strUnionSql1? & ? " ?From?(( " ? & ?strFinalUnion_TB
????strUnionSql1? = ?strUnionSql1? & ? " ?Join? " ? & ?strContractOwner_TB? & ? " ?On?tbFinalUnion.CContractid=tbContractOwner.CContractid) "
????strUnionSql1? = ?strUnionSql1? & ? " ?Join? " ? & ?strOwner_TB? & ? " ?On?tbContractOwner.COwnerid=tbOwner.COwnerid) "
????strUnionSql1? = ?strUnionSql1? & ? " ?Join? " ? & ?strPayMode_TB? & ? " ?On?tbOwner.CModeCode=tbPayMode.CModeCode "
????
???? ' ???步驟三:
???? ' ???????按?CPlaceCode?Asc,COwner?排序
????strUnionSql1? = ?strUnionSql1? & ? " ?Order?By?CPlaceCode?Asc,COwner "
????
???? ' ???SQL拼裝邏輯全部完成:
???? ' ???????記錄條件SQL,用於外部調試
????m_strFinalUnionSql? = ?strFinalUnion_TB
????m_strResult1Sql? = ?strUnionSql
????m_strResult2Sql? = ?strUnionSql1
????
???? ' *********************************************
???? ' 代入考察期參數
???? ' ???處理輸入參數
???? Dim ?strDate? As ? String ,?strStartDate? As ? String ,?strEndDate? As ? String ,?datDate
????strDate? = ?strYear? & ? " - " ? & ?strMonth? & ? " - " ? & ? " 01 " ????? ' 2006-01-01
????datDate? = ? CDate (strDate)
????
????strDate? = ?Format(datDate,? " yyyymm " )
????strStartDate? = ?strDate? & ? " 01 "
????strEndDate? = ?Format( DateAdd ( " d " ,? - 1 ,? DateAdd ( " m " ,? 1 ,?datDate)),? " yyyymmdd " )
???? ' ???代入輸入參數
????strUnionSql? = ? Replace (strUnionSql,? " @Date6 " ,? " ' " ? & ?strDate? & ? " ' " )
????strUnionSql? = ? Replace (strUnionSql,? " @StartDate8 " ,? " ' " ? & ?strStartDate? & ? " ' " )
????strUnionSql? = ? Replace (strUnionSql,? " @EndDate8 " ,? " ' " ? & ?strEndDate? & ? " ' " )
????strUnionSql1? = ? Replace (strUnionSql1,? " @Date6 " ,? " ' " ? & ?strDate? & ? " ' " )
????strUnionSql1? = ? Replace (strUnionSql1,? " @StartDate8 " ,? " ' " ? & ?strStartDate? & ? " ' " )
????strUnionSql1? = ? Replace (strUnionSql1,? " @EndDate8 " ,? " ' " ? & ?strEndDate? & ? " ' " )
????
????
???? ' 查詢並設置傳出值
???? ' rsPlace?=?strUnionSql
???? ' rsOwner?=?strUnionSql1
????
???? If ?objADO.QueryData(strUnionSql,?rsPlace)? = ? False ? Then ? GoTo ?ErrHandler
???? If ?objADO.QueryData(strUnionSql1,?rsOwner)? = ? False ? Then ? GoTo ?ErrHandler
????
????fGetFreePlace? = ? True
???? Exit ? Function
ErrHandler:
????fGetFreePlace? = ? False
????objCommon.WriteErrLog?TheMdlName,? " fGetFreePlace " ,?fGetFreePlace,?Err.Number,?Err.Description
End?Function

' 用於調試用的SQL
Public ? Function ?getFreeSql(ByRef?strFreeSql? As ?Variant)? As ? Boolean
????strFreeSql? = ?m_strFreeSql
????getFreeSql? = ? True
End?Function
Public ? Function ?getEndSql(ByRef?strEndSql? As ?Variant)? As ? Boolean
????strEndSql? = ?m_strEndSql
????getEndSql? = ? True
End?Function
Public ? Function ?getFinalUnionSql(ByRef?strFinalUnionSql? As ?Variant)? As ? Boolean
????strFinalUnionSql? = ?m_strFinalUnionSql
????getFinalUnionSql? = ? True
End?Function
Public ? Function ?getResult1Sql(ByRef?strResult1Sql? As ?Variant)? As ? Boolean
????strResult1Sql? = ?m_strResult1Sql
????getResult1Sql? = ? True
End?Function
Public ? Function ?getResult2Sql(ByRef?strResult2Sql? As ?Variant)? As ? Boolean
????strResult2Sql? = ?m_strResult2Sql
????getResult2Sql? = ? True
End?Function

??????看這段代碼需要的是耐心，順著邏輯一步步地往下走才行。
??????代碼中對基礎表的封裝，可以看 str[表名]_TB 這樣命名的變量，它將一張表封裝起來，然後重要的一點就是逐層的表命名，這裡使用了原表的名稱，雖然看起來有些混亂，但是這是調試所必需的。?

??????後記：
?????????這一次的嚐試，在時間上消耗比較大，開始時對於結果的正確性還有著一絲懷疑，不過在完工後也就釋然了，只有一處比較小的筆誤進行過調試，算是對這次的結構上的一點肯定吧，嘻嘻。
?????????哎，不過測試時還是遇到了令人沮喪的事情，這次測試MM最終對我說任務很忙，俺的測試報告自己寫就好，咳咳。
?????????如果有仔細看完上面的SQL源碼，不難看出除了在結構上完成了表及表間邏輯的封裝外，而且將最終形成的視圖邏輯也進行了封裝，因此在ASP視圖中僅僅需要完成循環顯示即可，這除了簡化ASP視圖外，還對後面幾次的需求變動造成了很深的影響，決定了日後變動的走向。