Common Table Expressions(CTE)

Common Table Expressions(CTE)為 SQL SERVER 2005新增語法之一，不過聽說ORACLE從8i or 9i就已有支援且此語法為T-SQL標準語法非微軟自創的語法，在研討會上講師總是說明CTE可用來取得暫存Table及Table變數，但我覺得它應被用在遞迴搜尋上，講師也有示範一段語法但未加以說明程式各區段用法，所以我找了一些資料並參考網路上搜尋到的組織圖來試一下，讓Select完後的結果有階層式效果。

組織圖

 

圖片出處:http://www.hsmc.com.tw/company_%20profile/profile10.htm 華昕電子公司組織圖。

 

組織資料表格，欄位依序為 單位代碼、單位名稱、上級單位，單位階級

 

先看看我想表示的結果，組織從第０階依序開始展開。

  

如要達到此效果在還沒有認識CTE前，我的做法可能會寫支StoredProcedure和Function(遞迴用)來搭配使用，但有了 CTE 後似乎一切都變得很簡單了，不相信嗎...來看看下面的語法，只要下面語法一跑完，上面的結果立即呈現。

 

   1:  WITH corg(UnitId,UnitName,LevelNo,SerialNum,SerialStr)

   2:  AS

   3:  (

   4:  Select UnitID,UnitName,LevelNo,SerialNum = CAST(row_number() over (order by UnitId) as int),

   5:         CAST(row_number() over (order by UnitId) as varchar(255))

   6:    From CompanyOrg with(nolock)

   7:   Where LevelNo = 0

   8:  UNION ALL

   9:  Select t1.UnitId,t1.UnitName,t1.LevelNo,

  10:         CAST(t2.SerialNum + 1 as int),CAST(RTrim(t2.SerialStr) + '-' +

  11:         CAST(row_number() over (order by t1.UnitID) as varchar(10)) as varchar(255))

  12:    From CompanyOrg t1 inner join corg t2

  13:      on t1.SuperiorID = t2.UnitId

  14:   Where t1.LevelNo > 0

  15:  )

  16:  Select UnitId,UnitName = REPLICATE('－',LevelNo) + Rtrim(UnitName),LevelNo from corg 

  17:  order by SerialStr

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { background-color: #f7fa7e; }

程式第１行 : 宣告CTE的名稱為 corg，且包含欄位有 UnitId,UnitName,LevelNo,SerialNum,SerialStr。

UnitId = 組織代碼
UnitName = 組織名稱                       
LevelNo = 組織層級                       
SerialNum = 同層級中的順序                       
SerialStr = 整體排序用的字串

程式第４～７行 : 此區域為"錨點"資料，就是不需要參與遞迴的資料，一般是最上或最下層資料。

程式第８行 : 如要以CTE 來跑遞迴式資料時，一定要用UNION ALL 唷。

程式第９～１４行 : 此區域為要做遞迴的語法，以４～７行做出來的corg遂一在CompanyOrg 搜尋下一階，然後一直遞迴下去直到都沒有下一階為止。(SQL SERVER 系統預設最大遞迴階層100階，但可設定到32767階)

程式第１６行 : 將結果 corg 顯示出來，重點在於SerialStr這個欄位，決定結果的順序是否正確。

ROW_NUMBER() 這也是SQL SERVER 2005 新的語法，在之前版本如想在資料集自定排序用序號時，一定會使用到Identity，但有時為了要將identity值塞進Table(#Table)中，必須多跑一段語法來處理，但現在有這函式可以不需要再多加一個步驟就能排序(不依整體Order by 排序時)。

row_number() over (order by UnitId) = 依Unitd來給序號。