通用資料表運算式CTE

 

CTE是一個暫存且具名的結果集。

CTE主要分成兩個部份，第一部份為CTE本身的定義，

第二部份為使用此CTE的外部操作。

 

語法：

[ WITH <expression_name [ (column_name [,...n] ) ] AS (CTE_query_definition) > [,...n] ]

 

先行準備範例資料表

CREATE TABLE EMPLOYEE 
  ( 
     no   INT NOT NULL, 
     name VARCHAR(20) NOT NULL, 
     sir  INT NULL, 
     PRIMARY KEY(no) 
  ) 

INSERT dbo.EMPLOYEE VALUES (1, 'Chen Bill', NULL)
INSERT dbo.EMPLOYEE VALUES (2, 'Hun Tim', 4)
INSERT dbo.EMPLOYEE VALUES (3, 'Lin Fin', 2)
INSERT dbo.EMPLOYEE VALUES (4, 'Chen Po', 1)
INSERT dbo.EMPLOYEE VALUES (5, 'Sun Kim', 12)
INSERT dbo.EMPLOYEE VALUES (6, 'Lu Reco', 10)
INSERT dbo.EMPLOYEE VALUES (7, 'Chen Leo', 1)
INSERT dbo.EMPLOYEE VALUES (8, 'Hu Jim', 10)
INSERT dbo.EMPLOYEE VALUES (9, 'Wu Zero', 10)
INSERT dbo.EMPLOYEE VALUES (10, 'Lin Mary', 7)
INSERT dbo.EMPLOYEE VALUES (11, 'Lu Ji', 10)
INSERT dbo.EMPLOYEE VALUES (12, 'Ja Tuees', 7)

 

範例一、將CTE當成暫存的View，查詢姓「Chen」的員工

SELECT no,name,sir FROM   EMPLOYEE;

WITH Chen_EMPOLYEE (編號,姓名,主管)
AS
(
    SELECT no,name,sir 
    FROM   EMPLOYEE 
    WHERE  name LIKE 'Chen%' 
)
SELECT 編號,姓名,主管 FROM   CHEN_EMPOLYEE

 

範例二、透過CTE來UPDATE資料，將姓「Chen」的員工改姓「Wang」

SELECT no,name,sir FROM   EMPLOYEE;

WITH Chen_EMPOLYEE (編號,姓名,主管)
AS
(
    SELECT no,name,sir 
    FROM   EMPLOYEE 
    WHERE  name LIKE 'Chen%' 
)
UPDATE CHEN_EMPOLYEE 
SET    姓名 = Replace(姓名, 'Chen', 'Wang') 

SELECT no,name,sir FROM   EMPLOYEE

 

範例三、同時使用多個CTE，選出姓「Lin」與姓「Wang」的員工

SELECT no,name,sir FROM   EMPLOYEE;

WITH Wang_EMPOLYEE (編號,姓名,主管)
AS
(
    SELECT no,name,sir 
    FROM   EMPLOYEE 
    WHERE  name LIKE 'Wang%' 
)
,
Lin_EMPOLYEE (編號,姓名,主管)
AS
(
    SELECT no,name,sir 
    FROM   EMPLOYEE 
    WHERE  name LIKE 'Lin%' 
)
SELECT 編號,姓名,主管 FROM   Lin_EMPOLYEE 
UNION ALL
SELECT 編號,姓名,主管 FROM   Wang_EMPOLYEE

 

範例四、遞迴CTE，查詢每一個員工上司

SELECT no,name,sir FROM   EMPLOYEE;

WITH CTE_REC (主管編號,主管姓名,員工編號,員工姓名,層級)
AS
(
    SELECT sir,Cast(NULL AS VARCHAR(20)),no,name,1 AS level 
    FROM   EMPLOYEE 
    WHERE  sir IS NULL 

    UNION ALL

    SELECT E.sir,CTE_REC.員工姓名,E.no,E.name,層級 + 1 
    FROM   EMPLOYEE E 
           INNER JOIN CTE_REC 
                   ON E.sir = CTE_REC.員工編號 
    WHERE  E.sir IS NOT NULL 
)

SELECT 主管編號,主管姓名,員工編號,員工姓名,層級
FROM CTE_REC ORDER BY 層級, 員工編號
GO

其原理為遞迴第一輪將「錨點成員」也就是最資深員工Chen Bill放進CTE資料表中，

接下來換「遞迴成員」將根據CTE資料表找出其主管是Chen Bill的員工將之放進CTE資料表中。

之後的每一輪的「遞迴成員」將根據CTE資料表的主管們，把所有員工給列進CTE資料表中。

 

TIPS：

1、CTE只能被使用一次

2、錨點成員與遞迴成員之間只能使用UNION ALL集合運算子

 

參考資料：

WITH common_table_expression (Transact-SQL)