大大毛 的笔记

  DDM's Note

哪怕没有办法一定有说法,
就算没有鸽子一定有乌鸦,
固执无罪 梦想有价,
让他们惊讶.

posts - 14, comments - 23, trackbacks - 0, articles - 58
   :: 首页 ::  :: 联系 ::  :: 管理

外聯陷阱

Posted on 2007-01-19 12:47 大大毛 阅读(272) 评论(0)  编辑  收藏 所属分类: SQL
前言
      這裡講的外聯指的是 Left/Right Join,這兩個是等效的,出於習慣用 Left Join。在遇到需要使用外聯的地方,大家通常會按照 Inner Join 的思路來進行改造,特別是在原有的內聯邏輯需要轉變到外聯邏輯的時候。雖然有某些時候內聯改外聯只要變關鍵字就好,但遺憾的是絕大部分情況都不會如此簡單,得要小心從事才行。下面就我遇到過的例舉一二:

DDL
示例用DDL
   表間關係是:
      合約--場地;合約-(合約房東)-房東;
   說明:
      各表的 CDelFlag 字段是一個刪除標識,CDelFlag='N' 的記錄才是可用的。
      場地的 CIsUse 字段是個類型標識,下面的示例中會引用到。


示例1
    邏輯:
         搜尋在指定時段內結束的合約房東。
    代入參數:
        1.PlaceCode:場地的PK,Like匹配;
        2.StartDate:開始日期;
        3.EndDate:結束日期。

   DML
DML:
    
-- 原邏輯:搜尋在指定時段內結束的(有效)合約房東資料

     Select
            
*  
        
From

            tbContractRent 
Join  tbContractOwner  On  tbContractRent.CContractId = tbContractOwner.CContractId
            
Join  tbOwner  On  tbContractOwner.COwnerId =
tbOwner.COwnerId
        
Where

            tbContractRent.CDelFlag
= ' N '
            
And  tbContractOwner.CDelFlag = ' N '
            
And  tbOwner.CDelFlag = ' N '
            
And  tbContractRent.CEndDate  Between   @StartDate   And   @EndDate
            
And  tbContractRent.CPlaceCode  Like   ' %@PlaceCode% '

       可以看到,這段DML代碼的邏輯是非常簡單的,構造也很容易,就是將 tbContractRent 與 tbOwner 通過中間表 tbContractOwner來連接,只要代入參數以及實現 CDelFlag = 'N' 就好。

      邏輯變更:
         搜尋在指定時段內結束的所有(有效)合約且帶出房東資料(部分合約無匹配的房東資料)
         根據邏輯此時要做的是需要將上面的內聯改成外聯關係。
-- 陷阱1:
     Select
            
*
        
From
            tbContractRent 
Left   Join  tbContractOwner  On  tbContractRent.CContractId = tbContractOwner.CContractId
            
Join  tbOwner  On  tbContractOwner.COwnerId =
tbOwner.COwnerId
        
Where

            tbContractRent.CDelFlag
= ' N '
            
And  tbContractOwner.CDelFlag = ' N '
            
And  tbOwner.CDelFlag = ' N '
            
And  tbContractRent.CEndDate  Between   @StartDate   And   @EndDate
            
And  tbContractRent.CPlaceCode  Like   ' %@PlaceCode% '
         上面SQL的意圖很明顯,是想將合約左聯中間表,再關聯到房東。但是改造的結果是失敗,失敗的原因很明顯,就是3表是逐步關聯起來的,只將合約與中間表實現了左聯,但與房東表的關聯卻沒有改動,現在重新改動一下。
-- 陷阱2:
     Select
            
*
        
From
            tbContractRent 
Left   Join  tbContractOwner  On  tbContractRent.CContractId = tbContractOwner.CContractId
            
Left   Join  tbOwner  On  tbContractOwner.COwnerId =
tbOwner.COwnerId
        
Where

            tbContractRent.CDelFlag
= ' N '
            
And  tbContractOwner.CDelFlag = ' N '
            
And  tbOwner.CDelFlag = ' N '
            
And  tbContractRent.CEndDate  Between   @StartDate   And   @EndDate
            
And  tbContractRent.CPlaceCode  Like   ' %@PlaceCode% '
         這一次已經將3張表的關聯都改成了外聯,可是還是失敗,因為你無論怎麼查詢都會發現它與第1條SQL的內聯是等效的,為什麼會這樣呢?原因很簡單,在SQL語法中 Join 是連接關係,它會在 Where 子句之前完成,因此這條SQL的工作鎏程變成這樣:
         a.三表之間外聯 --> b.Where
         步驟a形成的外聯結果集是包含全部的 tbContractRent 記錄的,未連接上的 tbContractOwner,tbOwner 表記錄的全部字段的值都會是 Null ,再從這個記錄上選取 tbOwner.CDelFalg='N'...,無疑就將這些 Null 值的記錄給排除掉了,產生了與 Inner Join 等效的結果
         原因說清楚,就好解決。首先看 CDelFlag = 'N',這個條件,它在邏輯中屬於“前置”條件,應該在連接表時進行。
    解決方案
     Select
            
*
        
From
            tbContractRent 
Left   Join  tbContractOwner  On  tbContractRent.CContractId = tbContractOwner.CContractId  And  tbContractOwner.CDelFlag = ' N '
            
Left   Join  tbOwner  On  tbContractOwner.COwnerId = tbOwner.COwnerId  And  tbOwner.CDelFlag = ' N '
        
Where
            tbContractRent.CDelFlag
= ' N '
            
And  tbContractRent.CEndDate  Between   @StartDate   And   @EndDate
            
And  tbContractRent.CPlaceCode  Like   ' %@PlaceCode% '
      說明:由於合約表是關聯的入口表(即 Left 的左表),因此它的 CDelFlag='N' 可以放在後部實現篩選。


示例2:
      邏輯:
         搜尋異常數據,查找有合約卻沒有對應房東的記錄,前置條件是該合約所在場所的 CIsUse='N'
   DML:
-- 陷阱1:
     Select
            tbContractRent.
*
        
From
            tbContractRent 
Left   Join  tbPlace  on  tbContractRent.CPlaceCode = tbPlace.CPlaceCode
            
Left   Join  tbContractOwner  on  tbContractRent.CContractid =
tbContractOwner.CContractid
            
Left   Join  tbOwner  on  tbContractOwner.COwnerid =
tbOwner.COwnerid
        
Where

            tbPlace.CIsUse
= ' N '
            
And  tbContractOwner.CContractid  is   null
         結果是失敗的,它並沒有按照設定的邏輯來正常工作。參照示例1中的解釋,問題應該是出在 tbPlace.CIsUse='N' 這個Where條件上,該條件將需要搜尋的數據全部給過濾出去了,那麼按照示例1的方法將該條件前置能夠解決問題嗎?
-- 陷阱2:
     Select
            tbContractRent.
*
        
From
            tbContractRent 
Left   Join  tbPlace  on  tbContractRent.CPlaceCode = tbPlace.CPlaceCode  And  tbPlace.CIsUse = ' N '
            
Left   Join  tbContractOwner  on  tbContractRent.CContractid = tbContractOwner.CContractid
            
Left   Join  tbOwner  on  tbContractOwner.COwnerid =
tbOwner.COwnerid
        
Where

            tbContractOwner.CContractid 
is   null
         條件前置,這是前例中的解決方案,但它不是萬能的,需要依據邏輯來判斷是否合適。在該示例中如果將 tbPlace.CIsUse='N' 條件前置,雖然可以先排除掉不符合該條件的 tbPlace 記錄,但是 Left Join 關鍵字卻會將它們重新給拉回來,只不過拉回來的結果是除了合約表的記錄外,其它字段全部為 Null 。正好完全符合下面的 tbContractOwner.CContractid is null 條件,很有欺騙性吧。
    解決方案
     Select
            tbContractRent.
*
        
From
            tbContractRent 
Left   Join  tbPlace  on  tbContractRent.CPlaceCode = tbPlace.CPlaceCode
            
Left   Join  tbContractOwner  on  tbContractRent.CContractid =
tbContractOwner.CContractid
            
Left   Join  tbOwner  on  tbContractOwner.COwnerid =
tbOwner.COwnerid
        
Where

            tbContractOwner.CContractid 
is   null
            
And  (tbPlace.CIsUse = ' N '
                
OR  tbPlace.CPlaceCode  is   null
            )
         這個有一點點復雜:根據邏輯 tbPlace.CIsUse='N' 這個條件是後置條件,應該放在後部,但是僅僅這樣處理就會產生陷阱1的效果,對應於合約的 Left Join 關聯,需要加入 OR tbPlace.CPlaceCode is null 來維護外聯的結果集。

只有注册用户登录后才能发表评论。


网站导航:
 

i am ddm