日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
Oracle Handbook系列之結(jié)構(gòu)化查詢

一)準(zhǔn)備測試數(shù)據(jù)

閑話少說，直入正題。建立一張簡單的職工表 t_hierarchical:

  • Emp 職工編號
  • Mgr 職工的直接上司(Mgr本身也是職工)
  • Emp_name 職工姓名

插入一些測試數(shù)據(jù)，除了大老板AA，其它的職工都各有自己的Manager。

 
 
 
      
  
  
  1. select emp, mgr, emp_name from t_hierarchical t; 
    2.  
 

 
 
 
      
  
  
  1. 1            AA 
    2.   
  
  
  2. 2     1     BB 
    3.   
  
  
  3. 3     2     CC 
    4.   
  
  
  4. 4     3     DD 
    5.   
  
  
  5. 5     2     EE 
    6.   
  
  
  6. 6     3     FF 
    7.

二)CONNECT BY

 
 
 
      
  
  
  1. select emp, mgr, LEVEL from t_hierarchical t 
    2.   
  
  
  2. CONNECT BY PRIOR emp=mgr 
    3.   
  
  
  3. order by emp; 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1           1 
    6.   
  
  
  6. 2     1     2 
    7.   
  
  
  7. 2     1     1 
    8.   
  
  
  8. 3     2     1 
    9.   
  
  
  9. 3     2     3 
    10.   
  
  
  10. 3     2     2 
    11.   
  
  
  11. 4     3     4 
    12.   
  
  
  12. 4     3     1 
    13.   
  
  
  13. 4     3     2 
    14.   
  
  
  14. 4     3     3 
    15.   
  
  
  15. 5     2     3 
    16.   
  
  
  16. 5     2     2 
    17.   
  
  
  17. 5     2     1 
    18.   
  
  
  18. 6     3     2 
    19.   
  
  
  19. 6     3     3 
    20.   
  
  
  20. 6     3     4 
    21.   
  
  
  21. 6     3     1 
    22.

解釋一下，CONNECT BY用于指定 父-子 記錄的關(guān)系(PRIOR我們在下例中解釋，更直觀一些)。舉emp 2為例，他隸屬于emp 1，如果我們以emp 1為根節(jié)點，顯然LEVEL=2;以emp 2自身為根節(jié)點，則LEVEL=1，這就是為什么上述查詢結(jié)果中出現(xiàn)共色標(biāo)識部分那兩行記錄，其它的類推。

三)START WITH

通常我們需要更直觀、更具有實用性的結(jié)果，這需要用到結(jié)構(gòu)化查詢中的START WITH子句，用于指定根節(jié)點：

 
 
 
      
  
  
  1. select emp, mgr, LEVEL from t_hierarchical t 
    2.   
  
  
  2. START WITH emp=1 
    3.   
  
  
  3. CONNECT BY PRIOR emp=mgr; 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1           1 
    6.   
  
  
  6. 2     1     2 
    7.   
  
  
  7. 3     2     3 
    8.   
  
  
  8. 4     3     4 
    9.   
  
  
  9. 6     3     4 
    10.   
  
  
  10. 5     2     3 
    11.

這里我們指定了根節(jié)點是emp 1，這樣的結(jié)果直觀了許多，例如，以emp 1為根節(jié)點，那么emp 3位于第三級(emp 1—emp 2—emp 3)，這里補充一下 PRIOR 關(guān)鍵字的說明，個人觀點：“PRIOR emp=mgr”表示前一條記錄的emp編號 = 當(dāng)前記錄的mgr編號，從查詢結(jié)果中可以看出這一點。同時，從查詢結(jié)果中還能發(fā)現(xiàn)明顯的 遞歸 痕跡，參見不同顏色標(biāo)識的數(shù)字。

四)SYS_CONNECT_BY_PATH()

不得不介紹一下非常牛波依的SYS_CONNECT_BY_PATH()函數(shù)，我們可以得到層次結(jié)構(gòu)或者說樹狀結(jié)構(gòu)的 路徑， 參見如下：

 
 
 
      
  
  
  1. select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH(emp,'/') path from t_hierarchical t 
    2.   
  
  
  2. START WITH emp=1 
    3.   
  
  
  3. CONNECT BY PRIOR emp=mgr; 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1            1     /1 
    6.   
  
  
  6. 2     1     2     /1/2 
    7.   
  
  
  7. 3     2     3     /1/2/3 
    8.   
  
  
  8. 4     3     4     /1/2/3/4 
    9.   
  
  
  9. 6     3     4     /1/2/3/6 
    10.   
  
  
  10. 5     2     3     /1/2/5 
    11.

五)CONNECT_BY_ISLEAF

非常好用的CONNECT_BY_ISLEAF虛列。何謂LEAF(葉子)，即沒有任何節(jié)點隸屬于該節(jié)點：

 
 
 
      
  
  
  1. select emp, mgr, LEVEL, SYS_CONNECT_BY_PATH(emp,'/') path from t_hierarchical t 
    2.   
  
  
  2. where CONNECT_BY_ISLEAF=1 
    3.   
  
  
  3. START WITH emp=1 
    4.   
  
  
  4. CONNECT BY PRIOR emp=mgr; 
    5.   
  
  
  5.  
    6.   
  
  
  6. 4     3     4     /1/2/3/4 
    7.   
  
  
  7. 6     3     4     /1/2/3/6 
    8.   
  
  
  8. 5     2     3     /1/2/5 
    9.

#p#

六)CONNECT BY與WHERE子句

下面再說說，關(guān)于引入結(jié)構(gòu)化查詢后，SQL語句的執(zhí)行順序問題，根據(jù)Oracle文檔，先后是：

1)JOIN，無論用的是JOIN ON的寫法，還是在WHERE中做的關(guān)聯(lián)

2)CONNECT BY

3)其它的WHERE條件

看一個例子，假設(shè)上面的各位職工，需要保存一些注釋信息，同時這些信息根據(jù)中文、英文分成兩個不同版本，我們可以簡單設(shè)計一下這個注釋表：

 
 
 
      
  
  
  1. |-Emp 職工編號 
    2.   
  
  
  2. |-Lang 語言（中文或英文） 
    3.   
  
  
  3. |-Emp_desc 職工的具體描述 
    4.   
  
  
  4.  
    5.   
  
  
  5. select emp, lang, emp_desc from t_desc; 
    6.   
  
  
  6.  
    7.   
  
  
  7. 1     chinese 這是注釋 
    8.   
  
  
  8. 1     english   this is comment 
    9.   
  
  
  9. 2     chinese 這是注釋 
    10.   
  
  
  10. 2     english   this is comment 
    11.   
  
  
  11. 3     chinese 這是注釋 
    12.   
  
  
  12. 3     english   this is comment 
    13.   
  
  
  13. 4     chinese 這是注釋 
    14.   
  
  
  14. 4     english   this is comment 
    15.   
  
  
  15. 5     chinese 這是注釋 
    16.   
  
  
  16. 5     english   this is comment 
    17.   
  
  
  17. 6     chinese 這是注釋 
    18.   
  
  
  18. 6     english   this is comment 
    19.

現(xiàn)在需要在原有的職工結(jié)構(gòu)化查詢中包括每個職工的中文注釋信息，我們看看下面的查詢：

 
 
 
      
  
  
  1. select t.emp, t.mgr, td.emp_desc, LEVEL 
    2.   
  
  
  2. from t_hierarchical t, t_desc td 
    3.   
  
  
  3. where t.emp=td.emp and td.lang='chinese' 
    4.   
  
  
  4. START WITH t.emp=1 
    5.   
  
  
  5. CONNECT BY PRIOR t.emp=t.mgr; 
    6.   
  
  
  6.  
    7.   
  
  
  7. 1            chinese 這是注釋 1 
    8.   
  
  
  8. 2     1     chinese 這是注釋 2 
    9.   
  
  
  9. 3     2     chinese 這是注釋 3 
    10.   
  
  
  10. 4     3     chinese 這是注釋 4 
    11.   
  
  
  11. 6     3     chinese 這是注釋 4 
    12.   
  
  
  12. 4     3     chinese 這是注釋 4 
    13.   
  
  
  13. 6     3     chinese 這是注釋 4 
    14.   
  
  
  14. 5     2     chinese 這是注釋 3 
    15.   
  
  
  15. 3     2     chinese 這是注釋 3 
    16.   
  
  
  16. 4     3     chinese 這是注釋 4 
    17.   
  
  
  17. 6     3     chinese 這是注釋 4 
    18.   
  
  
  18. 4     3     chinese 這是注釋 4 
    19.   
  
  
  19. 6     3     chinese 這是注釋 4 
    20.   
  
  
  20. 5     2     chinese 這是注釋 3 
    21.   
  
  
  21. 2     1     chinese 這是注釋 2 
    22.   
  
  
  22. 3     2     chinese 這是注釋 3 
    23.   
  
  
  23. 4     3     chinese 這是注釋 4 
    24.   
  
  
  24. 6     3     chinese 這是注釋 4 
    25.   
  
  
  25. 4     3     chinese 這是注釋 4 
    26.   
  
  
  26. 6     3     chinese 這是注釋 4 
    27.   
  
  
  27. 5     2     chinese 這是注釋 3 
    28.   
  
  
  28. 3     2     chinese 這是注釋 3 
    29.   
  
  
  29. 4     3     chinese 這是注釋 4 
    30.   
  
  
  30. 6     3     chinese 這是注釋 4 
    31.   
  
  
  31. 4     3     chinese 這是注釋 4 
    32.   
  
  
  32. 6     3     chinese 這是注釋 4 
    33.   
  
  
  33. 5     2     chinese 這是注釋 3 
    34.

再看這個查詢，看起來與前者是一樣的：

 
 
 
      
  
  
  1. select t.emp, t.mgr, td.emp_desc, LEVEL 
    2.   
  
  
  2. from t_hierarchical t join t_desc td 
    3.   
  
  
  3. on (t.emp=td.emp and td.lang='chinese') 
    4.   
  
  
  4. START WITH t.emp=1 
    5.   
  
  
  5. CONNECT BY PRIOR t.emp=t.mgr; 
    6.   
  
  
  6.  
    7.   
  
  
  7. 1            這是注釋 1 
    8.   
  
  
  8. 2     1     這是注釋 2 
    9.   
  
  
  9. 3     2     這是注釋 3 
    10.   
  
  
  10. 4     3     這是注釋 4 
    11.   
  
  
  11. 6     3     這是注釋 4 
    12.   
  
  
  12. 5     2     這是注釋 3 
    13.

第二個是我們期望的結(jié)果，第二個則相去甚遠。追究原因，是因為前一個例子中第二個條件 td.lang=’chinese’不被認為是JOIN條件，所以在CONNECT BY之后執(zhí)行；后一個例子中由于顯式地把第二個條件寫在了JOIN ON子句中，所以它在CONNECT BY之前執(zhí)行。

由于缺少第二個條件的JOIN（即本節(jié)***例）會導(dǎo)致每個的職工出現(xiàn)兩次，換一個數(shù)據(jù)少一點的例子，看看CONNECT BY遇到這樣的重復(fù)數(shù)據(jù)的時候是怎么處理的。

 
 
 
      
  
  
  1. select emp, mgr, lang from t2; 
    2.   
  
  
  2.  
    3.   
  
  
  3. 1            chinese 
    4.   
  
  
  4. 1            english 
    5.   
  
  
  5. 2     1     chinese 
    6.   
  
  
  6. 2     1     english 
    7.

CONNECT BY之后：

 
 
 
      
  
  
  1. select emp, mgr, lang from t2 
    2.   
  
  
  2. start with emp=1 
    3.   
  
  
  3. connect by prior emp=mgr; 
    4.   
  
  
  4.  
    5.   
  
  
  5. 1            chinese 
    6.   
  
  
  6. 2     1     chinese 
    7.   
  
  
  7. 2     1     english 
    8.   
  
  
  8. 1            english 
    9.   
  
  
  9. 2     1     chinese 
    10.   
  
  
  10. 2     1     english 
    11.

lang=’chinese’過濾之后：

 
 
 
      
  
  
  1. 1            chinese 
    2.   
  
  
  2. 2     1     chinese 
    3.   
  
  
  3. 2     1     chinese 
    4.

出現(xiàn)重復(fù)行，顯然不是我們期望的結(jié)果。

七)CONNECT BY LEVEL

下面我再來看看一個特殊的用法 CONNECT BY LEVEL，這是一個理解起來令人頭痛，但同時在某些情境下又是非常有用的：

 
 
 
      
  
  
  1. select LEVEL from dual CONNECT BY LEVEL<=6; 
    2.   
  
  
  2.  
    3.   
  
  
  3. 1 
    4.   
  
  
  4. 2 
    5.   
  
  
  5. 3 
    6.   
  
  
  6. 4 
    7.   
  
  
  7. 5 
    8.   
  
  
  8. 6 
    9.

如果你以前從未使用過，但是不幸你猜中了結(jié)果，我深表佩服，我至今沒有想通，事實上，它甚至不太符合結(jié)構(gòu)化查詢CONNECT BY的語法，因為根據(jù)Oracle文檔，CONNECT BY條件中至少有一個表達式要使用PRIOR關(guān)鍵字。 以至于有人覺得CONNECT BY LEVEL是一個BUG，懷疑Oracle可能在后續(xù)的版本中加以糾正。

無論如何，CONNECT BY LEVEL在Oracle 10g/11g中運行良好，如果你不想費勁想通這其中的原由，可以簡單地把想認為是構(gòu)造了一個循環(huán)，因此如果你寫成CONNECT BY 1=1，則會輸出1到無窮大的數(shù)。


當(dāng)前文章：Oracle Handbook系列之結(jié)構(gòu)化查詢
當(dāng)前路徑：http://m.5511xx.com/article/cocceho.html