使用公用表表示式（CTE）WITH AS提高sql效能，with as【未完待續】

定義：

         WITH AS短語，也叫做子查詢部分（subquery factoring），可以讓你做很多事情，定義一個SQL片斷，該SQL片斷會被整個SQL語句所用到。有的時候，是為了讓SQL語句的可讀性更高些，也有可能是在UNION ALL的不同部分，作為提供資料的部分。 
        特別對於UNION ALL比較有用。因為UNION ALL的每個部分可能相同，但是如果每個部分都去執行一遍的話，則成本太高，所以可以使用WITH AS短語，則只要執行一遍即可。如果WITH AS短語所定義的表名被呼叫兩次以上，則優化器會自動將WITH AS短語所獲取的資料放入一個TEMP表裡，如果只是被呼叫一次，則不會。而提示materialize則是強制將WITH AS短語裡的資料放入一個全域性臨時表裡。很多查詢通過這種方法都可以提高速度。

語法：

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

簡化一下就是：;with 別名 as() [select,insert,update]

案例：

if object_id('tb')is not null drop table tb
go
create table tb(姓名 varchar(10),課程 varchar(10),分數 int)
insert into tb values('張三','語文',74)
insert into tb values('張三','數學',83)
insert into tb values('張三','物理',93)
insert into tb values('李四','語文',74)
insert into tb values('李四','數學',84)
insert into tb values('李四','物理',94)

go

select * from tb

姓名         課程         分數
---------- ---------- -----------
張三         語文         74
張三         數學         83
張三         物理         93
李四         語文         74
李四         數學         84
李四         物理         94

(6 行受影響)

隨便寫的一個例子，僅僅為了說明而已：求李四的總分和平均分

;with tmp as
(
select 分數 from tb where 姓名 = '李四'
)
 select sum(分數) 總分,CAST(avg(分數) as decimal(18,2)) 平均分 from tmp 

總分          平均分
----------- ---------------------------------------
252         84.00


(1 行受影響)

說明：

1. CTE後面必須直接跟使用CTE的SQL語句（如select、insert、update等），否則，CTE將失效。

2. CTE後面也可以跟其他的CTE，但只能使用一個with，多個CTE中間用逗號（,）分隔。

3. 如果CTE的表示式名稱與某個資料表或檢視重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE，當然，後面的SQL語句使用的就是資料表或檢視了。

4. CTE 可以引用自身，也可以引用在同一 WITH 子句中預先定義的 CTE。不允許前向引用。

5. 不能在 CTE_query_definition 中使用以下子句：
（1）COMPUTE 或 COMPUTE BY
（2）ORDER BY（除非指定了 TOP 子句）
（3）INTO
（4）帶有查詢提示的 OPTION 子句
（5）FOR XML
（6）FOR BROWSE

6. 如果將 CTE 用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾。