sql with asの使い方について詳しく説明します。

一.WITH ASの意味WITH ASのフレーズは、サブクエリとも呼ばれ、多くのことをさせてSQLの断片を定義します。SQLの断片はSQL文全体で使用されます。場合によっては、SQL文の可読性を高くするために、ユニオンALLの異なる部分でデータを提供する部分として利用することもあります。特にユニオンALLには効果的です。ユニオンALLは部分ごとに同じかもしれませんが、部分ごとに一回実行すれば、コストが高すぎるので、WITH ASのフレーズが使えます。一回だけ実行すればいいです。WITH ASフレーズで定義されている表名が二回以上呼び出された場合、自動的にWITH ASフレーズで取得したデータをTEMPテーブルに入れます。一回だけ呼び出されたら、できません。提示したmaterializeは強制的にWITH ASフレーズのデータをグローバル臨時表に入れます。多くのクエリはこの方法で速度を上げることができます。二.使用方法は、まず次のネストされたクエリ文を参照してください。

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

上のクエリ文は、サブクエリを使用しています。このSQL文は複雑ではないですが、入れ子のレベルが多すぎるとSQL文は読みにくくなります。したがって、表変数の方式を使ってこの問題を解決することもできます。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文より複雑ですが、サブクエリを表変数@tに置いて、SQL文をより維持しやすくします。テーブル変数は実際に臨時テーブルを使用しているため、追加のI/Oオーバーヘッドが増加しているため、テーブル変数の方式はデータ量が大きく、頻繁に照会する場合にはあまり適していない。このために、SQL Server 2005にはもう一つの解決策が提供されています。これは、パブリック表式（CTE）であり、CTEを使用してSQL文のメンテナンス性を向上させることができます。以下はCTEの構文である。

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

は現在CTEを使用して上記の問題を解決している。SQL文は以下の通りである。

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

では、crは表変数と似ている表式を使用しているが、SQL Server 2005は共通表式を処理する方法で異なる。CTEを使用する際には、CTEの後にCTEを使用するSQL文（select、insert、udateなど）を直接フォローしなければならない。そうでなければ、CTEは無効になる。以下のSQL文のようにCTE：

 
with
cr as
(
select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.CountryRegion --     SQL    
--   CTE SQL         CTE   --
select * from person.StateProvince where CountryRegionCode in (select * from cr)

2.CTEの後には他のCTEと一緒にいても良いですが、一つのwithしか使えません。複数の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

。CTEの表現名がデータテーブルまたはビューの名前であれば、CTEの後に続くSQL文はもちろんCTEを使用します。後のSQL文は、データテーブルまたはビューを使用しています。下のSQL文に示されているように、

-- table1         
with
table1 as
(
select * from persons where age < 30
)
select * from table1 --      table1       
select * from table1 --      table1    

4.CTEは、自身を参照しても良いし、同じWITHサブルーチンで予め定義されているCTEを参照しても良いです。前への参照は許可されません。5.CTEにいてはいけないquery_definitionでは、（1）COMPUTEまたはCOMPUTE BY（2）ORDER BY（TOP子文が指定されていない限り）（3）INTO（4）クエリーの提示があるOPTTION子文（5）FOR BROW 6.CTE 132をバッチ処理の一部に属する文に使うなら、それ以前の文の末尾にはS 796が示されています。