CTE(Common Table Expression) 公用表表達式,它是在單個語句的執行範圍内定義的臨時結果集,隻在查詢期間有效。它可以自引用,也可在同一查詢中多次引用,實作了代碼段的重複利用。
CTE最大的好處是提升T-Sql代碼的可讀性,可以更加優雅簡潔的方式實作遞歸等複雜的查詢。
CTE可用于:
⒈ 建立遞歸查詢,這個應該是CTE最好用的地方
⒉ 在同一語句中多次引用生成的表
3. 減少子查詢和表變量,提高執行效率
CTE優點:
1. 使用 CTE 可以獲得提高可讀性和輕松維護複雜查詢的優點。同時,CTE要比表變量的效率高得多。
2. 可以用來定義一個SQL片斷,該SQL片斷會被整個SQL語句所用到。有的時候,是為了讓SQL語句的可讀性更高些,也有可能是在UNION ALL的不同部分,作為提供資料的部分。
3. 查詢可以分為單獨塊、簡單塊、邏輯生成塊。之後,這些簡單塊可用于生成更複雜的臨時 CTE,直到生成最終結果集。
下面是CTE的文法:
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
)
使用示例
1. 查詢臨時結果集
WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)
AS (
SELECT CategoryID
,CategoryName
,ParentID
,CategoryLevel
FROM Category(NOLOCK)
WHERE Status = 1 and parentid = 23
)
select * from cte;
注意: 1.使用CTE的SQL語句應緊跟在相關的CTE後面。
2.多重CTE中間用逗号,分隔。
3.可以被緊跟着的一條SQL語句所使用多次,但不能被緊跟着的多條SQL語句使用。
2. 建立遞歸查詢
WITH cte(CategoryID ,CategoryName,ParentID,CategoryLevel)
AS (
SELECT CategoryID
,CategoryName
,ParentID
,CategoryLevel
FROM Category(NOLOCK)
WHERE Status= 1 and parentid in (21,22,23,25,26)
UNION ALL
SELECT t.CategoryID
,t.CategoryName
,t.ParentID
,t.CategoryLevel
FROM Category(NOLOCK) AS t
INNER JOIN cte AS c ON t.parentid = c.CategoryID where Status= 1
)
select * from cte;
3. cte結果集和資料表關聯
WITH cte(CategoryID,CategoryName,ParentID,CategoryLevel)
AS (
SELECT CategoryID
,CategoryName
,ParentID
,CategoryLevel
FROM Category(NOLOCK)
WHERE Status = 1 and parentid = 23
)
select p.ProductId,p.ProductName,c.CategoryID,c.CategoryName,c.CategoryLevel
from product p(NOLOCK)
inner join cte c(NOLOCK) on p.CategoryId=c.CategoryID