CTE Select Command

從MSDN 參考資料:http://msdn.microsoft.com/zh-tw/library/ms186243.aspx
得知可以運用CTE查詢語法來處理遞迴資料表，原文對使用語法已有詳盡說明，
本文由筆者將專案上實作的案例，整理出幾個常用的查詢，並提供一個範例資料庫，
讓有興趣瞭解的朋友方便學習。

首先請下載: 範例資料庫
這是一個SQL 2005所建立的資料庫，直接解壓縮將 SystemLead.bak 從SSMS中[還原]，
完成後計有以下資料庫物件:

資料結構有：部門主檔、人員主檔、部門成員關係檔，其中部門主檔上是做遞迴設計。


接下來可以直接執行預存程序來瞭解如何運用一般查詢語法找藏在遞迴中的資料。
有 5 個預存程序範例，分別為:

• 01-找出最上層部門

  WITH loopReport(DEP_ID, UPPER_ID, DEP_LEVEL)
  AS
  (
  SELECT DEP_ID, UPPER_DEPT_ID, 0 AS DEP_LEVEL FROM SL_DEP WHERE UPPER_DEPT_ID IS NULL 
   
  )
  SELECT * FROM loopReport;

• 02-找出各階層部門 

  WITH loopReport(DEP_ID, UPPER_ID, DEP_LEVEL, DEP_PATH)
  AS
  (
  /*頂層*/
  SELECT DEP_ID, UPPER_DEPT_ID, 0 AS DEP_LEVEL, CAST(DEP_ID AS VARCHAR(MAX))
  FROM SL_DEP WHERE UPPER_DEPT_ID IS NULL 
   
  UNION ALL
   
  /*下級各層*/
  SELECT D.DEP_ID, D.UPPER_DEPT_ID, L.DEP_LEVEL+1, CAST(L.DEP_PATH+' > '+D.DEP_ID AS VARCHAR(MAX))
  FROM SL_DEP D
  INNER JOIN loopReport L ON D.UPPER_DEPT_ID=L.DEP_ID 
   
  )
  SELECT * FROM loopReport;

  
  
• 03-找出各階層部門_及所屬成員 

  WITH loopReport(DEP_ID, UPPER_ID, DEP_LEVEL, DEP_PATH, MEMBER_NAME)
  AS
  (
  /*頂層*/
  SELECT DEP_ID, UPPER_DEPT_ID, 0 AS DEP_LEVEL, CAST(DEP_ID AS VARCHAR(MAX)), CAST(NULL AS VARCHAR(MAX))
  FROM SL_DEP WHERE UPPER_DEPT_ID IS NULL 
   
  UNION ALL
   
  /*下級各層*/
  SELECT D.DEP_ID, D.UPPER_DEPT_ID, L.DEP_LEVEL+1, CAST(L.DEP_PATH+' > '+D.DEP_ID AS VARCHAR(MAX)), CAST(NULL AS VARCHAR(MAX))
  FROM SL_DEP D
  INNER JOIN loopReport L ON D.UPPER_DEPT_ID=L.DEP_ID 
   
  UNION ALL
  /*各部門的成員*/
  SELECT CAST(NULL AS VARCHAR(64)), D.DEP_ID, L.DEP_LEVEL+1, L.DEP_PATH , CAST(U.FULL_NAME AS VARCHAR(MAX))
  FROM dbo.SL_DEP_USER D
  INNER JOIN dbo.SL_USER U ON D.USER_NAME=U.USER_NAME
  INNER JOIN loopReport L ON U.DEP_ID=L.DEP_ID
  )
  SELECT DEP_PATH, MEMBER_NAME 
  FROM loopReport
  WHERE MEMBER_NAME IS NOT NULL
  ORDER BY DEP_PATH;

  
  
• 04-找出某一部門以下各階層的部門

  WITH loopReport(DEP_ID, UPPER_ID, DEP_LEVEL, DEP_PATH)
  AS
  (
  /*頂層*/
  SELECT DEP_ID, UPPER_DEPT_ID, 0 AS DEP_LEVEL, CAST(DEP_ID AS VARCHAR(MAX))
  FROM SL_DEP WHERE UPPER_DEPT_ID = @DEP_ID
   
  UNION ALL
   
  /*下級各層*/
  SELECT D.DEP_ID, D.UPPER_DEPT_ID, L.DEP_LEVEL+1, CAST(L.DEP_PATH+' > '+D.DEP_ID AS VARCHAR(MAX))
  FROM SL_DEP D
  INNER JOIN loopReport L ON D.UPPER_DEPT_ID=L.DEP_ID 
  )
  SELECT * FROM loopReport
  ORDER BY DEP_PATH;

• 05-判斷使用者是否為部門及含下階的成員

  WITH loopReport(DEP_ID, UPPER_ID, DEP_PATH)
  AS
  (
  /*所指定的部門*/
  SELECT DEP_ID, UPPER_DEPT_ID, CAST(DEP_ID AS VARCHAR(MAX))
  FROM SL_DEP WHERE UPPER_DEPT_ID = @DEP_ID
   
  UNION ALL
   
  /*下級各層*/
  SELECT D.DEP_ID, D.UPPER_DEPT_ID, CAST(L.DEP_PATH+' > '+D.DEP_ID AS VARCHAR(MAX))
  FROM SL_DEP D
  INNER JOIN loopReport L ON D.UPPER_DEPT_ID=L.DEP_ID 
   
  )
  SELECT D.*, L.DEP_PATH 
  FROM loopReport L
  INNER JOIN SL_DEP_USER D ON D.DEP_ID=L.DEP_ID
  WHERE D.USER_NAME=@USER_NAME;

使用小祕訣:

• 在新增計算欄位時資料型別須一致，若有不一致時需要使用轉型(CONVERT 或 CAST)來處理。