在編寫(xiě)T-SQL代碼時(shí)，往往需要臨時(shí)存儲(chǔ)某些結(jié)果集。前面我們已經(jīng)廣泛使用和介紹了兩種臨時(shí)存儲(chǔ)結(jié)果集的方法：臨時(shí)表和表變量。除此之外，還可以使用公用表表達(dá)式的方法。

公用表表達(dá)式(Common Table Expression)是SQL Server2005版本的引入的一個(gè)特性。CTE可以看組是一個(gè)臨時(shí)的結(jié)果集，可以再接下來(lái)來(lái)的一個(gè)SELECT,INSERT,UPDATE,DELETE,MERGE語(yǔ)句中多次引用。

一、3種方法比較

使用公用表達(dá)式CTE可以讓語(yǔ)句更加清晰簡(jiǎn)練。與公用表達(dá)式作用類似的還有臨時(shí)表和表變量。下面給出三種方法的對(duì)比。

• 臨時(shí)表#：需要在臨時(shí)數(shù)據(jù)庫(kù)TempDB中通過(guò)I/O操作來(lái)創(chuàng)建表結(jié)構(gòu)，一旦用戶退出SQL Server環(huán)境則自動(dòng)被刪除。
• 表變量@：在內(nèi)存中以表結(jié)構(gòu)的形式存在，其定義與變量一致，其使用與表類似，不需要產(chǎn)生I/O。
• 公用表表達(dá)式with as：定義在內(nèi)存中保存的臨時(shí)存儲(chǔ)結(jié)果集對(duì)象，不產(chǎn)生I/O，不需要按照表變量這樣定義，使用方法和表類似?？梢宰约阂?，也可以再查詢中被多次引用。

1、使用CTE好處

根據(jù)微軟對(duì)CTE好處的描述，可以歸結(jié)為四點(diǎn):

• 可以定義遞歸公用表表達(dá)式(CTE)
• 當(dāng)不需要將結(jié)果集作為視圖被多個(gè)地方引用時(shí)，CTE可以使其更加簡(jiǎn)潔
• GROUP BY 語(yǔ)句可以直接作用于子查詢所得的標(biāo)量列
• 可以在一個(gè)語(yǔ)句中多次引用公用表表達(dá)式(CTE)

二、WITH AS的含義

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

它用于定義一個(gè)SQL片段，該片段會(huì)被是整個(gè)SQL語(yǔ)句所用到。如果WITH AS所以定的表名被調(diào)用兩次以上，則優(yōu)化器會(huì)自動(dòng)將WITH AS所獲取的數(shù)據(jù)放入臨時(shí)表里，如果只是被調(diào)用一次，則不會(huì)。

可以通過(guò)materialize將WITH AS短語(yǔ)里的數(shù)據(jù)強(qiáng)制放入全局臨時(shí)表里。

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

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

三、CTE的定義

CTE的定義語(yǔ)法如下，主要包括3個(gè)部分。

• Expression_name:CTE表達(dá)式的名稱。
• Column_name:列名列表。
• CTE_query_definition:定義CTE結(jié)果集的Select查詢語(yǔ)句

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

按照是否遞歸，可以將公用表（CTE）表達(dá)式分為遞歸公用表表達(dá)式和非遞歸公用表表達(dá)式.

1、非遞歸公用表表達(dá)式(CTE)：

非遞歸公用表表達(dá)式（CTE）是查詢結(jié)果僅僅一次性返回一個(gè)結(jié)果集用于外部查詢調(diào)用。并不在其定義的語(yǔ)句中調(diào)用其自身的CTE。

非遞歸公用表表達(dá)式（CTE）的使用方式和視圖以及子查詢一致。

比如一個(gè)簡(jiǎn)單的非遞歸公用表表達(dá)式:

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

公用表表達(dá)式的好處之一是可以在接下來(lái)一條語(yǔ)句中多次引用:

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;

雖然以上引用了多次，但是只是一條語(yǔ)句，所以可以正常執(zhí)行。

如果多條語(yǔ)句引用，如下面這樣，是會(huì)報(bào)錯(cuò)的。

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

輸出結(jié)果如下：

由于CTE只能在接下來(lái)一條語(yǔ)句中使用，因此，當(dāng)需要接下來(lái)的一條語(yǔ)句中引用多個(gè)CTE時(shí)，可以定義多個(gè)，中間用逗號(hào)分隔。下面是一次定義多個(gè)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;

結(jié)果如下：

2、遞歸公用表表達(dá)式(CTE)：

對(duì)于遞歸公用表達(dá)式來(lái)說(shuō)，只需要在語(yǔ)句中定義兩部分：

• 基本語(yǔ)句
• 遞歸語(yǔ)句

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

現(xiàn)在使用CTE查詢其每個(gè)欄目是第幾層欄目的代碼如下：

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

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

select * from @table1;

with COL_CTE(Id, Name, ParentId, tLevel) as (
    --基本語(yǔ)句
    select id, Name, ParentId, 0 as tLevel from @table1 where ParentId=0
    union all
    --遞歸語(yǔ)句
    select c.id, c.Name, c.ParentId, ce.tLevel+1 as tLevel from @table1 as c
    inner join COL_CTE as ce --遞歸調(diào)用
    on c.ParentId=ce.Id)

select * from COL_CTE;

輸出結(jié)果如下：

0表示頂級(jí)欄目。1就是1級(jí)欄目。語(yǔ)法非常優(yōu)雅。就一個(gè)SELECT * FRON COL_CTE。這正是CTE強(qiáng)大的地方，但是，這要有約束，否則如果無(wú)限制遞歸可以會(huì)消耗掉非常多的系統(tǒng)資源。下面來(lái)看看如何限制遞歸的最大次數(shù)。

如將上面的查詢語(yǔ)法改為：

WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本語(yǔ)句
    SELECT Id,Name,ParentId,0 AS tLevel FROM @table1 WHERE ParentId = 0
    UNION ALL
    --遞歸語(yǔ)句
    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)　　--指定最大遞歸次數(shù)為2

我們知道在上面的查詢中，要查到天河區(qū)新聞最少要遞歸3次，但是現(xiàn)在只遞歸2次，運(yùn)行是什么結(jié)果呢？

提示信息如下：

消息 530，級(jí)別 16，狀態(tài) 1，第 1 行
　　語(yǔ)句被終止。完成執(zhí)行語(yǔ)句前已用完最大遞歸 2。

CTE是一種十分優(yōu)雅的存在。CTE所帶來(lái)最大的好處是代碼可讀性的提升,這是良好代碼的必須品質(zhì)之一。使用遞歸CTE可以更加輕松愉快的用優(yōu)雅簡(jiǎn)潔的方式實(shí)現(xiàn)復(fù)雜的查詢。

到此這篇關(guān)于SQL Server中T-SQL公用表表達(dá)式(CTE)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論