MSSQL-通用資料表運算式CTE(Common Table Expression)

CTE最主要有二個用途：簡化子查詢與遞迴查詢

（一）簡化子查詢

針對複雜的SQL語法，通常會使用到多層子查詢，

一開始寫時，還記得為什麼要這樣寫，

但過一段時間後或其他人來閱讀時，可能就無法馬上知道內容。

所以可用CTE來簡化SQL語法，以利閱讀且方便驗證，舉例如下：

多層子查詢

SELECT Column_A1 FROM Table_A WHERE Column_A2 IN
 (
    SELECT Column_B1 FROM Table_B WHERE Column_B2 IN
         (SELECT Column_C1 FROM Table_C WHERE Column_C2 LIKE '%Sample%')
 )

利用CTE改寫

;WITH CTE_C AS
      (SELECT Column_C1 FROM Table_C WHERE Column_C2 LIKE '%Sample%')
,CTE_B AS
      (SELECT Column_B1 FROM Table_B WHERE Column_B2 IN (SELECT * FROM CTE_C))
SELECT Column_A1 FROM Table_A WHERE Column_A2 IN (SELECT * FROM CTE_B)

由上述例子可見，雖然CTE語法可能字數不會少於子查詢語法，但閱讀上比起子查詢語法夾雜在主查詢語法中更簡潔易讀。

（二）遞迴查詢

有時會遇到多階層關係的資料，可能會想知道層級，這時就可以用CTE遞迴來實現。

測試資料建立：

--存在則刪除
IF OBJECT_ID('EMPLOYEE', 'U') IS NOT NULL
	DROP TABLE EMPLOYEE;
GO

--建立員工資料表
CREATE TABLE EMPLOYEE
(
	DEPT_NO VARCHAR(10) NOT NULL,
	EMPLOYEE_NO   VARCHAR(10) NOT NULL,
	EMPLOYEE_NAME NVARCHAR(20) NOT NULL,
	TITLE NVARCHAR(20) NOT NULL,
	SUPERIOR_NO VARCHAR(20) NOT NULL
);

--寫入員工資料
INSERT INTO EMPLOYEE (DEPT_NO,EMPLOYEE_NO, EMPLOYEE_NAME, TITLE, SUPERIOR_NO)
VALUES
	('A0100','00001', N'王總明', N'總經理', ''),
	('A0110','01001', N'陳啟明', N'經理', '00001'),
	('A0110','01010', N'蘇志成', N'組長', '01001'),
	('A0110','05101', N'詹姆士', N'專員', '01010'),
	('A0110','05102', N'林約翰', N'專員', '01010'),
	('A0120','02001', N'王麗莎', N'經理', '00001'),
	('A0120','02010', N'吳明識', N'組長', '02001'),
	('A0120','05201', N'簡東昕', N'專員', '02010'),
	('A0120','05202', N'鄭經任', N'專員', '02010');
--查詢
SELECT * FROM EMPLOYEE;

遞迴SQL語法

;WITH EMPLOYEE_CTE AS (
	--找出最高層級
	SELECT EMPLOYEE_NO, EMPLOYEE_NAME, TITLE, SUPERIOR_NO, 1 AS EMPLOYEE_LEVEL
	FROM EMPLOYEE WHERE SUPERIOR_NO = ''
	UNION ALL
	--跟 CTE 自身做遞迴 JOIN 
	SELECT A.EMPLOYEE_NO, A.EMPLOYEE_NAME, A.TITLE, A.SUPERIOR_NO
		, (B.EMPLOYEE_LEVEL + 1) AS EMPLOYEE_LEVEL --職位層級+1
	FROM EMPLOYEE A
	INNER JOIN EMPLOYEE_CTE B ON A.SUPERIOR_NO = B.EMPLOYEE_NO
)
SELECT * FROM EMPLOYEE_CTE ORDER BY EMPLOYEE_LEVEL

執行結果如下：