工作中遇到的問題和解決辦法9

07年6月11日

(1)  怎麼建三張表的聯合left join結果卻都在一個視圖裡顯示的這樣的視圖

解釋一下上面這個是什麼意思吧,有三張表

TsysDepartInf部門表

departId   int   (4)    主鍵    

departParentId    int     (4)    父結點       （下面的繼續討論時加的欄位）

departname     varchar(50)  部門名稱  

departIsEnd     varchar(2)  是否是葉子節點

isDel     varchar(2)  是否刪除

TsysUserInf使用者表

userId     int     (4)  主鍵

username   varchar(50)  使用者名稱稱

TuserInf2TDept部門使用者關聯表

id    int    (4)   主鍵

departId    int   (4)  部門表主鍵

userId     int     (4)  使用者表主鍵

根據需求,要把部門和使用者的資訊的結果都放在一個視圖裡面,然後用ajax的第三方開源樹遍曆這些資訊,在一棵樹中顯示這些資訊.恩,該怎麼解決?建視圖吧

(SELECT dbo.TsysDepartInf.departId tid, dbo.TsysDepartInf.departName tname,
      dbo.TsysDepartInf.departIsEnd tisend, dbo.TsysDepartInf.isDel tisdel
FROM dbo.TsysDepartInf left join dbo.TuserInf2TDept on dbo.TsysDepartInf.departId = dbo.TuserInf2TDept.departId)
union
(select dbo.TsysUserInf.userId tid, dbo.TsysUserInf.userName tname, null tisend, null tisdel
from dbo.TsysUserInf left join dbo.TuserInf2TDept on dbo.TsysUserInf.userId = dbo.TuserInf2TDept.userId)

這是建視圖的語句,但是SQLServer2000裡,通過圖形介面建視圖,無法儲存,會報"視圖無法解析union"的錯;但是在它的查詢分析器裡,執行這句話確是完全可行的!

那麼,只要把上面的語句加句話放在查詢分析器裡執行了

CREATE VIEW dbo.V_SHOW
  AS
SELECT dbo.TsysDepartInf.departId tid, dbo.TsysDepartInf.departName tname,
      dbo.TsysDepartInf.departIsEnd tisend, dbo.TsysDepartInf.isDel tisdel
FROM dbo.TsysDepartInf left join dbo.TuserInf2TDept on dbo.TsysDepartInf.departId = dbo.TuserInf2TDept.departId
union
select dbo.TsysUserInf.userId tid, dbo.TsysUserInf.userName tname, null tisend, null tisdel
from dbo.TsysUserInf left join dbo.TuserInf2TDept on dbo.TsysUserInf.userId = dbo.TuserInf2TDept.userId

藍色的代碼是標準的建視圖語句!當在查詢分析器裡執行完這個語句,點擊"視圖"------->右鍵"重新整理",就會看到所建的視圖已經存在了,如果偏要"視圖"------>右鍵"建立視圖",那麼就會報"視圖無法解析union"的錯.這樣,你無法建的!

另外還要注意:

1>>>青色的標記,這些都是別名,只要別名一樣,結果就會在一起的,部門表要顯示的欄位又比使用者表多,當使用者表沒有與部門表對應的欄位時,就要設為null +別名,而不是部門表中真正的欄位名,如:dbo.TsysDepartInf.departIsEnd 和dbo.TsysDepartInf.isDel 這兩個欄位!

2>>>必須保證對應欄位類型的一致性,否則會出錯!

3>>>union 與union  all  的區別:

         union串連的時候,查詢的結果沒有重複的

         union  all  串連的時候,查詢結果是有重複的

看看結果吧:  哦,先說說我表裡有的資料!

我的部門表有的資料資訊是

departid       departName      departIsEnd       isDel

    1                   zhang                       0                       0

    2                   wang                        0                       0

    3                    jiang                        0                       0

使用者表的資訊是

   userId          username

      5                   zhang

      6                   wang

      7                   jiang

      8                   test

運行視圖的結果:

    tid                   tname                   tisend                    tisdel

     1                   zhang                          0                           0

     2                   wang                           0                           0

     3                   jiang                            0                           0    

     5                   zhang                         null                      null

     6                   wang                          null                      null

     7                   jiang                           null                      null

     8                   test                             null                      null

    繼續討論這顆樹的顯示問題：下面是我對這顆樹的視圖最佳化和hibernate映射問題的解決

從這顆樹顯示的角度考慮上面建的視圖吧。上面的視圖少了一個parentid也就是這顆樹的父結點，少了這個，嘿嘿。。。這顆樹是無法顯示出層級關係的，這是個大問題。剛才又最佳化了一下視圖，這個問題就解決了，比較perfect的視圖了。

樹的顯示問題：所有的使用者都應該在這個部門下面，而且使用者下面不再有任何層級關係了，也就是“所有的使用者都是這顆樹的葉子結點”。

還有就是部門下面可以在添加部門的，所以添加部門的時候判斷是否是葉子結點，如果是，那就javascript的alert一個提示資訊就OK了。

基於上面的考慮，只能重新改寫一下上面的視圖了，如下：

CREATE VIEW dbo.VajaxDepartTree  （把視圖改成這個名字了）
  AS
SELECT dbo.TsysDepartInf.departId tid, dbo.TsysDepartInf.departName tname,
      dbo.TsysDepartInf.departIsEnd tisend, dbo.TsysDepartInf.isDel tisdel, dbo.TsysDepartInf.departParentId tdepartparentid
FROM dbo.TsysDepartInf left join dbo.TuserInf2TDept on dbo.TsysDepartInf.departId = dbo.TuserInf2TDept.departId
union
select dbo.TsysUserInf.userId tid, dbo.TsysUserInf.userName tname, tisend=1, null tisdel, dbo.TsysDepartInf.departId tdepartparentid
from  dbo.TsysUserInf left join dbo.TuserInf2TDept on dbo.TsysUserInf.userId = dbo.TuserInf2TDept.userId
      left join dbo.TsysDepartInf on dbo.TsysDepartInf.departId = dbo.TuserInf2TDept.departId

青色的為改動後的，因為0在ajax這顆樹裡不是葉子結點，1是葉子結點，使用者必須是葉子結點，所以給別名 tisend=1賦值為1就行了，這樣，查出的資料中關於使用者資訊中的tisend就直接賦值為1了，不用在程式裡判斷了。

因為使用者和部門是有對應關係的，所以又加了dbo.TsysDepartInf.departId tdepartparentid，這樣每一個使用者都可以對應著部門id了，別名是tdepartparentid這個別名的真正欄位是部門表的departId，而不是dbo.TsysDepartInf.departParentId tdepartparentid 部門表的departParentId。這樣做是為了兼顧這個視圖的查詢結果是樹型的結構。很重要。

這樣，這個視圖就比較perfect了。

為了看效果，我把關聯表中的也加了資訊

     userId     departId

         8                 1

         7                  2

         6                 3

userId 5沒有做關聯。

運行結果：

 tid                   tname                   tisend                    tisdel            tdeparparentid

 

 

 

     1                   zhang                          0                           0                           0

     2                   wang                           0                           0                           0

     3                   jiang                            0                           0                           0

     6                   wang                          1                       null                           3

     7                   jiang                           1                      null                            2

     8                   test                             1                       null                           1

終於出來了，累死我了。當部門的tdeparparentid是0的時候，表示他是根目錄。

這樣可以用hibernate產生hbm設定檔了

<hibernate-mapping>
    <class name="com.yliso.hibernate.po.VajaxDepartTree" table="VajaxDepartTree" schema="dbo" catalog="yliso">
            <id name="tid" type="integer">
             <column name="tid" />             
           </id>
            <property name="tname" type="string">
                <column name="tname" length="32" />
            </property>
            <property name="tisend" type="string">
                <column name="tisend" length="1" />
            </property>
            <property name="tisdel" type="string">
                <column name="tisdel" length="2" />
            </property>
            <property name="tdepartparentid" type="integer">
                <column name="tdepartparentid" />
            </property>      
    </class>
</hibernate-mapping>

視圖沒有主鍵，會多一個PO複合主鍵的類，把那個類幹掉。設定檔裡把別名tid設為主鍵就行了，因為tid是沒有重複的，而且tid裡面有departId和userId的資料，你根據departId和userId中任何一個做主鍵，都不是最好的辦法。還是以tid做主鍵最好。好了，終於解決了。。。。。。這個問題昨天搞到半夜，今天又考慮得很全面，折騰一上午，累了。吃飯去了，不過問題很經典，解決了很有成就感，開心。

忘了說了，要直接在查詢分析器裡運行產生這個視圖，視圖的編譯器不認union，無法建。

而且直接在查詢分析器裡運行產生這個視圖這樣產生的視圖，在視圖裡圖形介面是看不到表及表關係，圖形介面看不到哪個欄位是顯示還是沒有顯示的。原因很簡單，SQLServer 2000裡面的視圖的設計器不支援union。

07年6月20日

繼續說說這顆樹，上面的解決辦法是可以顯示的，但是今天說的卻都是缺點，以至於我這顆樹不得不在項目中廢掉另建視圖解決，但是建立的視圖仍然無法在樹裡顯示出根據檔案分配使用者的關聯。。。。鬱悶ing。。。。

這顆樹結合現在的ajaxa動態樹的第3方控制項，是徹底的失敗了。。。因為結合第3方的控制項，當部門A下面還有部門B，並且部門A下面有使用者C，部門B下面有使用者C的時候，把這個全選中，傳過去的Id裡面有第3方控制項的一個隨機數，並且原來根據"，"號傳過去的id中，有類似這樣的情況：335，336，11187965_337,111199999_338,332,111188888_331,231,266,這樣的串出現，都是隨機的，再結合需求的變化。。。廢了這顆樹。如果不用第3方的控制項這顆樹是沒有問題的。。。。。。