SQL中WITH AS的使用實(shí)現(xiàn)

更新時(shí)間：2023年12月19日 11:50:12 作者：AD曼巴精神

WITH AS短語,也叫做子查詢部分,可以定義一個(gè)SQL片斷,該SQL片斷會被整個(gè)SQL語句用到,本文主要介紹了SQL中WITH AS的使用實(shí)現(xiàn),具有一定的參考價(jià)值,感興趣的可以了解一下

一．WITH AS的含義

WITH AS短語，也叫做子查詢部分（subquery factoring），可以定義一個(gè)SQL片斷，該SQL片斷會被整個(gè)SQL語句用到?？梢允筍QL語句的可讀性更高，也可以在UNION ALL的不同部分，作為提供數(shù)據(jù)的部分。

對于UNION ALL，使用WITH AS定義了一個(gè)UNION ALL語句，當(dāng)該片斷被調(diào)用2次以上，優(yōu)化器會自動將該WITH AS短語所獲取的數(shù)據(jù)放入一個(gè)Temp表中。而提示meterialize則是強(qiáng)制將WITH AS短語的數(shù)據(jù)放入一個(gè)全局臨時(shí)表中。很多查詢通過該方式都可以提高速度。

二．使用方法

先看下面一個(gè)嵌套的查詢語句：

select * from person.StateProvince 
where CountryRegionCode in 
(select CountryRegionCode from person.CountryRegion 
where Name like 'C%'
)

上面的查詢語句使用了一個(gè)子查詢。雖然這條SQL語句并不復(fù)雜，但如果嵌套的層次過多，會使SQL語句非常難以閱讀和維護(hù)。因此，也可以使用表變量的方式來解決這個(gè)問題，SQL語句如下：

declare @t table(CountryRegionCode nvarchar(3)) 
insert into @t(CountryRegionCode) 
(select CountryRegionCode from person.CountryRegion 
where Name like 'C%'
)

select * from person.StateProvince 
where CountryRegionCode in (select * from @t)

雖然上面的SQL語句要比第一種方式更復(fù)雜，但卻將子查詢放在了表變量@t中，這樣做將使SQL語句更容易維護(hù)，但又會帶來另一個(gè)問題，就是性能的損失。

由于表變量實(shí)際上使用了臨時(shí)表，從而增加了額外的I/O開銷，因此，表變量的方式并不太適合數(shù)據(jù)量大且頻繁查詢的情況。

為此，在SQL Server 2005中提供了另外一種解決方案，這就是公用表表達(dá)式（CTE），使用CTE，可以使SQL語句的可維護(hù)性，同時(shí)，CTE要比表變量的效率高得多。

下面是CTE的語法：

[ WITH <common_table_expression> [ ,n ] ] 
<common_table_expression>::= 
expression_name [ ( column_name [ ,n ] ) ] 
AS 
( CTE_query_definition )

現(xiàn)在使用CTE來解決上面的問題，SQL語句如下：

with cte as 
( 
select CountryRegionCode from person.CountryRegion 
where Name like 'C%' 
)

select * from person.StateProvince 
where CountryRegionCode in (select * from cte)

其中cte是一個(gè)公用表表達(dá)式，該表達(dá)式在使用上與表變量類似，只是SQL Server 2005在處理公用表表達(dá)式的方式上有所不同。

三. 語法注意

1、CTE后面必須直接跟使用CTE的SQL語句（如select、insert、update等），否則，CTE將失效。如下面的SQL語句將無法正常使用CTE：

with cte as
(
select CountryRegionCode from person.CountryRegion 
where Name like 'C%'
)

-- 加上這句會報(bào)錯(cuò)，應(yīng)將這條SQL語句去掉
select * from person.CountryRegion 

-- 使用CTE的SQL語句應(yīng)緊跟在相關(guān)的CTE后面 --
select * from person.StateProvince 
where CountryRegionCode in (select * from cte)

2、CTE后面也可以跟其他的CTE，但只能使用一個(gè)with，多個(gè)CTE中間用逗號（,）分隔，如下面的SQL語句所示：

with cte1 as
(
 select * from table1 where name like 'abc%'
),
cte2 as
(
 select * from table2 where id > 20
),
cte3 as
(
 select * from table3 where price < 100
)
select a.* 
from cte1 a, cte2 b, cte3 c 
where a.id = b.id 
and a.id = c.id

3、如果CTE的表達(dá)式名稱與某個(gè)數(shù)據(jù)表或視圖重名，則緊跟在該CTE后面的SQL語句使用的仍然是CTE

當(dāng)然，后面的SQL語句使用的就是數(shù)據(jù)表或視圖了，如下面的SQL語句所示：

-- table1是一個(gè)實(shí)際存在的表
with table1 as
(
    select * from persons where age < 30
)
select * from table1 -- 使用了名為table1的公共表表達(dá)式
select * from table1 -- 使用了名為table1的數(shù)據(jù)表

4、CTE 可以引用自身

也可以引用在同一 WITH 子句中預(yù)先定義的 CTE。不允許前向引用。

--使用遞歸公用表表達(dá)式顯示遞歸的多個(gè)級別
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;

--使用遞歸公用表表達(dá)式顯示遞歸的兩個(gè)級別
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 

--使用遞歸公用表表達(dá)式顯示層次列表
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort

--使用 MAXRECURSION 取消一條語句
--可以使用 MAXRECURSION 來防止不合理的遞歸 CTE 進(jìn)入無限循環(huán)。以下示例特意創(chuàng)建了一個(gè)無限循環(huán)，然后使用 MAXRECURSION 提示將遞歸級別限制為兩級
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2)
--在更正代碼錯(cuò)誤之后，就不再需要 MAXRECURSION。以下示例顯示了更正后的代碼
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte

5、不能在 CTE_query_definition 中使用以下子句：

（1）COMPUTE 或 COMPUTE BY

（2）ORDER BY（除非指定了 TOP 子句）

（3）INTO

（4）帶有查詢提示的 OPTION 子句

（5）FOR XML

（6）FOR BROWSE

如果將 CTE 用在屬于批處理的一部分的語句中，那么在它之前的語句必須以分號結(jié)尾，如下面的SQL所示：

declare @s nvarchar(3)
set @s = 'C%'
; -- 必須加分號
with
t_tree as
(
select CountryRegionCode from person.CountryRegion 
where Name like @s
)
select * from person.StateProvince 
where CountryRegionCode in (select * from t_tree)

到此這篇關(guān)于SQL中WITH AS的使用實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)SQL WITH AS內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: