天天看點

Transact-SQL學習筆記19——公用表表達式(CTE)

作者:包家三少

公用表表達式(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關鍵字

實驗環境如下兩表:

Transact-SQL學習筆記19——公用表表達式(CTE)

employee表

Transact-SQL學習筆記19——公用表表達式(CTE)

salary 表

WITH Test

AS

(SELECT * FROM employee WHERE age >40)

SELECT * FROM Test

Transact-SQL學習筆記19——公用表表達式(CTE)

内部命名方式

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

Transact-SQL學習筆記19——公用表表達式(CTE)

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

Transact-SQL學習筆記19——公用表表達式(CTE)

學習參考資料:《跟韓老師學 SQL Server 資料庫設計與開發》

繼續閱讀