SQL Server使用T-SQL進階之公用表表示式(CTE)

2022-05-21 16:00:15

在編寫T-SQL程式碼時，往往需要臨時儲存某些結果集。前面我們已經廣泛使用和介紹了兩種臨時儲存結果集的方法：臨時表和表變數。除此之外，還可以使用公用表表示式的方法。

公用表表示式(Common Table Expression)是SQL Server2005版本的引入的一個特性。CTE可以看組是一個臨時的結果集，可以再接下來來的一個SELECT,INSERT,UPDATE,DELETE,MERGE語句中多次參照。

一、3種方法比較

使用公用表示式CTE可以讓語句更加清晰簡練。與公用表示式作用類似的還有臨時表和表變數。下面給出三種方法的對比。

臨時表#：需要在臨時資料庫TempDB中通過I/O操作來建立表結構，一旦使用者退出SQL Server環境則自動被刪除。
表變數@：在記憶體中以表結構的形式存在，其定義與變數一致，其使用與表類似，不需要產生I/O。
公用表表示式with as：定義在記憶體中儲存的臨時儲存結果集物件，不產生I/O，不需要按照表變數這樣定義，使用方法和表類似。可以自己參照，也可以再查詢中被多次參照。

1、使用CTE好處

根據微軟對CTE好處的描述，可以歸結為四點:

可以定義遞迴公用表表示式(CTE)
當不需要將結果集作為檢視被多個地方參照時，CTE可以使其更加簡潔
GROUP BY 語句可以直接作用於子查詢所得的標量列
可以在一個語句中多次參照公用表表示式(CTE)

二、WITH AS的含義

WITH AS-做子查詢部分(subquery factoring)。

它用於定義一個SQL片段，該片段會被是整個SQL語句所用到。如果WITH AS所以定的表名被呼叫兩次以上，則優化器會自動將WITH AS所獲取的資料放入臨時表裡，如果只是被呼叫一次，則不會。

可以通過materialize將WITH AS短語裡的資料強制放入全域性臨時表裡。

WITH AS可以被緊跟著的一條SQL語句所使用多次，但不能被緊跟著的多條SQL語句使用。

WITH B AS 
(
    SELECT * FROM xxx WHERE Id > 5
)
SELECT * FROM B

三、CTE的定義

CTE的定義語法如下，主要包括3個部分。

Expression_name:CTE表示式的名稱。
Column_name:列名列表。
CTE_query_definition:定義CTE結果集的Select查詢語句

WITH expression_name [(column_name [,...n] )]
AS
( 
　　cte_query_definition 
)

按照是否遞迴，可以將公用表（CTE）表示式分為遞迴公用表表示式和非遞迴公用表表示式.

1、非遞迴公用表表示式(CTE)：

非遞迴公用表表示式（CTE）是查詢結果僅僅一次性返回一個結果集用於外部查詢呼叫。並不在其定義的語句中呼叫其自身的CTE。

非遞迴公用表表示式（CTE）的使用方式和檢視以及子查詢一致。

比如一個簡單的非遞迴公用表表示式:

WITH CTE_Test
AS
(
    SELECT * FROM Person_1
)
SELECT * FROM CTE_Test

公用表表示式的好處之一是可以在接下來一條語句中多次參照:

with CTE_Test 
 as (select * from Person_1)
select * from CTE_Test as a --第一次參照
     inner join CTE_Test as b --第二次參照
         on a.Id=b.Id
order by a.Id desc;

雖然以上參照了多次，但是隻是一條語句，所以可以正常執行。

如果多條語句參照，如下面這樣，是會報錯的。

with CTE_Test as (select * from Person_1)
select * from CTE_Test;
select * from CTE_Test;

輸出結果如下：

由於CTE只能在接下來一條語句中使用，因此，當需要接下來的一條語句中參照多個CTE時，可以定義多個，中間用逗號分隔。下面是一次定義多個CTE的例子：

with CTE_Test1  as (select * from Person_1), 
     CTE_Test2  as (select * from Person_2)
select * from CTE_Test1
union
select * from CTE_Test2;

結果如下：

2、遞迴公用表表示式(CTE)：

對於遞迴公用表示式來說，只需要在語句中定義兩部分：

基本語句
遞迴語句

先建一張表欄目表如下，欄目Id，欄目名稱，欄目的父欄目。

現在使用CTE查詢其每個欄目是第幾層欄目的程式碼如下：

declare @table1 table(id int, Name varchar(10), ParentId int);

insert into @table1(id, Name, ParentId)
values(1, '國內新聞', 0),
    (2, '廣東新聞', 1),
    (3, '廣州新聞', 2),
    (4, '天河新聞', 3),
    (5, '山東新聞', 1),
    (5, '青島新聞', 5);

select * from @table1;

with COL_CTE(Id, Name, ParentId, tLevel) as (
    --基本語句
    select id, Name, ParentId, 0 as tLevel from @table1 where ParentId=0
    union all
    --遞迴語句
    select c.id, c.Name, c.ParentId, ce.tLevel+1 as tLevel from @table1 as c
    inner join COL_CTE as ce --遞迴呼叫
    on c.ParentId=ce.Id)

select * from COL_CTE;

輸出結果如下：

0表示頂級欄目。1就是1級欄目。語法非常優雅。就一個SELECT * FRON COL_CTE。這正是CTE強大的地方，但是，這要有約束，否則如果無限制遞迴可以會消耗掉非常多的系統資源。下面來看看如何限制遞迴的最大次數。

如將上面的查詢語法改為：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本語句
    SELECT Id,Name,ParentId,0 AS tLevel FROM @table1 WHERE ParentId = 0
    UNION ALL
    --遞迴語句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM @table1 as c 
    INNER JOIN COL_CTE AS ce 
    ON c.ParentId = ce.Id
)

SELECT * FROM COL_CTE
OPTION(MAXRECURSION 2)　　--指定最大遞迴次數為2

我們知道在上面的查詢中，要查到天河區新聞最少要遞迴3次，但是現在只遞迴2次，執行是什麼結果呢？

提示資訊如下：

訊息 530，級別 16，狀態 1，第 1 行
　　語句被終止。完成執行語句前已用完最大遞迴 2。

CTE是一種十分優雅的存在。CTE所帶來最大的好處是程式碼可讀性的提升,這是良好程式碼的必須品質之一。使用遞迴CTE可以更加輕鬆愉快的用優雅簡潔的方式實現複雜的查詢。

到此這篇關於SQL Server中T-SQL公用表表示式(CTE)的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。