公用表表達式(Common Table Expression，CTE)和派生表類似，都是虛拟的表，但是相比于派生表，CTE 具有一些優勢和友善之處。
CTE 是使用 WITH子句定義的，包括三個部分:
- CTE名稱 cte_name、
- 定義CTE 的查詢語句inner query_definition
- 引用CTE的外部查詢語 outer query_definition
WITH cte_name [column name list]
AS
(inner querydefinition)
outer query definition
其中column name list指定 inner query_definition 中的列清單名，如果不寫該選項，則需要保證在 inner query_definition 中的列都有名稱且唯一，即對列名有兩種命名方式:内部命名和外部命名。
不能在定義 CTE 的查詢語句中使用 ORDER BY 子句，除非使用了 TOP關鍵字
實驗環境如下兩表：
employee表
salary 表
WITH Test
AS
(SELECT * FROM employee WHERE age >40)
SELECT * FROM Test
内部命名方式
WITH CTE_ep_sa
AS
(SELECT a.employee_id AS 員工ID,a.employee_name AS 姓名,
b.salary_type AS 工資類型,b.salary AS 工資
FROM employee a
INNER JOIN salary b
ON a.employee_id = b.employee_id)
SELECT * FROM CTE_ep_sa
WHERE 姓名 ='趙五'
外部命名方式
WITH CTE_ep_sa (員工ID,姓名,工資類型,工資)
AS
(SELECT a.employee_id ,a.employee_name ,
b.salary_type ,b.salary
FROM employee a
INNER JOIN salary b
ON a.employee_id = b.employee_id)
SELECT * FROM CTE_ep_sa
WHERE 姓名 ='趙五'
CTE兩個優勢
1.多次引用：避免重複書寫
雖然能夠多次引用定義好的 CTE，但是隻能在同一個外部查詢中引用而不能在同一批中的其他語句中引用。
WITH Test
AS
(SELECT * FROM employee )
SELECT a.employee_name,b.employee_name as leader_name FROM Test a
left join Test b
on a.leader_id = b.employee_id
2.多次定義：避免派生表嵌套問題
由于 CTE 隻能在接下來的一條語句中引用，是以當需要接下來的一條語句中引用多個CTE時，可以定義多個需要的CTE，不同的CTE用逗号分隔。
WITH CTE_1
AS
(SELECT * FROM employee ), --CTE定義語句1
CTE_2
AS
(SELECT * FROM salary ) --CTE定義語句1
SELECT * FROM CTE_1 a
left join CTE_2 b
on a.employee_id = b.employee_id
學習參考資料：《跟韓老師學 SQL Server 資料庫設計與開發》