複習 Common Table Expressions（CTE）

SQL Server 的階層式與遞迴查詢

Jasmin Ludolf

Content Developer

課程大綱

什麼是遞迴，如何使用？
如何用 Common Expression Tables（CTE）撰寫階層與遞迴查詢？
如何用 SQL 表示階層並加以查詢？
真實情境例子（如公司組織、物料清單、航班計畫）

俄羅斯娃娃與其遞迴特性。

什麼是 CTE？

CTE 的定義

指定一個臨時的具名結果集，稱為 common table expression（CTE）。它源自一個簡單查詢，並定義在單一陳述的執行範圍內。

CTE 的結構

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

CTE 的用途

管理複雜查詢
可用於 SELECT、INSERT、UPDATE、DELETE 陳述
可在一個 WITH 中定義多個 CTE
以 UNION 或 JOIN 結合多個 CTE
可取代檢視表（view）
自我參照資料表
遞迴查詢

為 IT 組織定義 CTE

使用 CTE 篩選管理職：

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

用 WITH AS 定義 CTE
對 Employee 資料表做 SELECT

為 IT 組織定義 CTE

選出薪資超過 10,000 的員工

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

為 IT 組織定義 CTE

完整查詢：

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

一起來練習吧！

SQL Server 的階層式與遞迴查詢