SQL Server with as使用
10176 ワード
一.WITH ASの意味
WITH ASフレーズは、サブクエリーセクション(subquery factoring)とも呼ばれ、SQL文全体に使用されるSQLセグメントを定義するために多くのことをすることができます.場合によっては、SQL文の読みやすさを向上させるために、UNIOALLの異なる部分で、データを提供する部分として使用される可能性があります.
特にユニオンALLには役立ちます.ユニオンALLの各部分は同じかもしれませんが、各部分が1回実行するとコストがかかりすぎますので、WITH ASフレーズを使ってもいいので、1回実行すればいいです.WITH ASフレーズで定義されたテーブル名が2回以上呼び出されると、オプティマイザはWITH ASフレーズで取得したデータを1つのTEMPテーブルに自動的に格納し、1回だけ呼び出されるとしません.ヒント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はテーブル変数よりも効率的です.
CTEの構文は次のとおりです.
[ WITH [ ,n ] ]
::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
CTEを使用して上記の問題を解決します.SQL文は次のとおりです.
ここでcrは共通テーブル式であり、この式は使用上テーブル変数と類似しているが、SQL Server 2005は共通テーブル式を処理する方法が異なる.
CTEを使用する場合、以下の点に注意してください.
1.CTEの後にCTEを使用するSQL文(select、insert、updateなど)が直接続く必要があります.そうしないと、CTEは無効になります.次のSQL文ではCTEが正常に使用されません.
2.CTEの後には他のCTEも使用できますが、次のSQL文に示すように、1つのwithしか使用できません.複数のCTEの間はカンマ(,)で区切られています.
3.CTEの式名がデータテーブルまたはビューと重複している場合、CTEのすぐ後ろにあるSQL文はCTEのまま使用されます.もちろん、後ろのSQL文はデータテーブルまたはビューを使用します.次のSQL文に示すように、次の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をバッチの一部に属する文に使用する場合、次のSQLに示すように、その前の文はセミコロンで終わる必要があります.
CTEはネストされたSQL文を簡略化するほか、再帰呼び出しも可能であり、この部分については後述する.
まず、次のデータテーブル(t_tree)を参照してください.
上の図は、id、node_の3つのフィールドを持つテーブルのデータを示しています.name、parent_id.実際、この表には省、市、区の3つの木型構造が保存されています.ここでidは、現在の省、市または区のid番号、node_を表すnameは名前、parent_を表しますidはノードの親ノードのidを表す.
省の下にあるすべての市と区(省を含む)を検索する必要があります.SQL文のみを使用して実装する場合は、カーソル、テンポラリ・テーブルなどのテクノロジーを使用する必要があります.しかし、SQL Server 2005ではCTEを使用して実装することもできます.
この需要から再帰呼び出しに該当する,すなわち,まず価格調整を満たす省の記録を検出し,本例では「遼寧省」の記録を調べると,以下のようになる.
そしてすべてのparentを調べますidフィールド値が1のレコードは、以下のようになります.
最後にparent_を調べますidフィールド値が2または3のレコードは、以下のようになります.
上の3つの結果セットをマージすると、最終結果セットになります.
上記の照会手順は、指定された省の記録(遼寧省)を先に調べて、この記録を得ると、対応するid値があり、次に入った再帰手順を下図に示すように再帰の過程で理解することもできる.
上記の例では、sqlを以下のように準備します.
再帰的なプロシージャは、union allを使用してクエリー結果セットをマージするプロシージャです.つまり、次の再帰式に相当します.
resultset(n) = resultset(n-1) union all current_resultset
ここでresultset(n)は最終結果セットを表し、resultset(n-1)は最後から2番目の結果セットを表し、current_resultsetは現在検出された結果セットを表し、最初に「遼寧省」を検索した記録セットは再帰的な初期条件に相当する.再帰的な終了条件はcurrent_ですresultsetが空です.次は、この再帰プロセスの疑似コードです.
以上の過程から,この再帰過程は比較的複雑に実現されているが,CTEはこの過程を簡略化するための簡単な構文を提供していることがわかる.
再帰を実現するCTE構文は次のとおりです.
三、CTE使用例
1.クエリー・ノードおよびその下のすべてのサブノード
2.サブノードがあるノードを問い合わせる
注:「遼寧省」と「瀋陽市」だけがサブノードを持っている.
3.MAXRECURSIONを使用して不合理な再帰を防止する
4.with asとinsertを組み合わせて使用
四、再帰CTE使用上の注意事項
再帰CTEの定義と使用には、次の点に注意してください.
1.再帰CTE定義には、少なくとも2つのCTEクエリー定義、1つの位置決めポイントメンバー、および1つの再帰メンバーが含まれる必要があります.複数の位置決めポイントメンバーと再帰メンバーを定義できます.ただし、すべてのロケーションポイントメンバークエリー定義は、最初の再帰メンバー定義の前に配置する必要があります.すべてのCTEクエリー定義は、ロケーションポイントメンバーですが、CTE自体を参照する場合を除きます.
2.位置決めポイントメンバーは、UNIOALL、UNION、INTERSECT、EXCEPTのいずれかの演算子と組み合わせて使用する必要があります.最後のアンカーポイントメンバーと最初の再帰メンバーの間、および複数の再帰メンバーを結合する場合は、UNIOALLコレクション演算子のみが使用されます.
3.ポイント・メンバーと再帰メンバーの列数は一致する必要があります.
4.再帰メンバー内のカラムのデータ型は、アンカーポイントメンバー内の対応するカラムのデータ型と一致する必要があります.
5.再帰メンバーのFROM句は一度だけCTE expression_を参照できます.name.
6.再帰メンバーのCTE_query_definitionでは、次の項目は許可されていません.
(1)SELECT DISTINCT
(2)GROUP BY
(3)HAVING
(4)スカラー重合
(5)TOP
(6)LEFT、RIGHT、OUTER JOIN(INNER JOINの出現を許可)
(7)サブクエリ
(8)CTE_への適用query_definitionのCTEの再帰的参照のヒント.
7.参加するSELECT文が返す列の空性にかかわらず、再帰CTEが返すすべての列は空欄とすることができる.
8.再帰CTEの組み合わせが正しくない場合、無限ループを引き起こす可能性がある.たとえば、再帰メンバー・クエリー定義が親カラムと子カラムに同じ値を返す場合、無限ループが発生します.MAXRECURSIONプロンプトおよびINSERT、UPDATE、DELETE、またはSELECT文のOPTION句のうちの1つの0から32767までの値を使用して、特定の文で許容される再帰級数を制限し、無限ループが発生しないようにすることができます.これにより,ループを生成するコード問題を解決する前に文の実行を制御することができる.サーバ範囲のデフォルト値は100です.0を指定した場合、制限はありません.文ごとに1つのMAXRECURSION値しか指定できません.
9.再帰共通テーブル式を含むビューを使用してデータを更新することはできません.
10.CTEを使用して、クエリ上でカーソルを定義できます.再帰CTEでは、クイック・イン・カーソルと静的(スナップショット)カーソルのみを使用できます.再帰CTEで他のカーソルタイプが指定されている場合、このタイプは静的カーソルタイプに変換されます.
11.CTEでリモート・サーバのテーブルを参照できます.CTEの再帰メンバーでリモート・サーバが参照されている場合、リモート・テーブルごとにスプールが作成され、ローカルでこれらのテーブルに繰り返しアクセスできます.
ここでは、SQL Serverでwith asの使用について説明します.
WITH ASフレーズは、サブクエリーセクション(subquery factoring)とも呼ばれ、SQL文全体に使用されるSQLセグメントを定義するために多くのことをすることができます.場合によっては、SQL文の読みやすさを向上させるために、UNIOALLの異なる部分で、データを提供する部分として使用される可能性があります.
特にユニオンALLには役立ちます.ユニオンALLの各部分は同じかもしれませんが、各部分が1回実行するとコストがかかりすぎますので、WITH ASフレーズを使ってもいいので、1回実行すればいいです.WITH ASフレーズで定義されたテーブル名が2回以上呼び出されると、オプティマイザはWITH ASフレーズで取得したデータを1つのTEMPテーブルに自動的に格納し、1回だけ呼び出されるとしません.ヒント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はテーブル変数よりも効率的です.
CTEの構文は次のとおりです.
[ WITH [ ,n ] ]
::=
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を使用する場合、以下の点に注意してください.
1.CTEの後にCTEを使用するSQL文(select、insert、updateなど)が直接続く必要があります.そうしないと、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も使用できますが、次のSQL文に示すように、1つのwithしか使用できません.複数のCTEの間はカンマ(,)で区切られています.
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の式名がデータテーブルまたはビューと重複している場合、CTEのすぐ後ろにあるSQL文はCTEのまま使用されます.もちろん、後ろのSQL文はデータテーブルまたはビューを使用します.次の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)クエリプロンプト付きOPTION句
(5)FOR XML
(6)FOR BROWSE
6.CTEをバッチの一部に属する文に使用する場合、次の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) CTEはネストされたSQL文を簡略化するほか、再帰呼び出しも可能であり、この部分については後述する.
まず、次のデータテーブル(t_tree)を参照してください.
上の図は、id、node_の3つのフィールドを持つテーブルのデータを示しています.name、parent_id.実際、この表には省、市、区の3つの木型構造が保存されています.ここでidは、現在の省、市または区のid番号、node_を表すnameは名前、parent_を表しますidはノードの親ノードのidを表す.
省の下にあるすべての市と区(省を含む)を検索する必要があります.SQL文のみを使用して実装する場合は、カーソル、テンポラリ・テーブルなどのテクノロジーを使用する必要があります.しかし、SQL Server 2005ではCTEを使用して実装することもできます.
この需要から再帰呼び出しに該当する,すなわち,まず価格調整を満たす省の記録を検出し,本例では「遼寧省」の記録を調べると,以下のようになる.
id node_name parent_id
1 0 そしてすべてのparentを調べますidフィールド値が1のレコードは、以下のようになります.
id node_name parent_id
2 1
3 1 最後にparent_を調べますidフィールド値が2または3のレコードは、以下のようになります.
id node_name parent_id
4 2
5 2
6 2 上の3つの結果セットをマージすると、最終結果セットになります.
上記の照会手順は、指定された省の記録(遼寧省)を先に調べて、この記録を得ると、対応するid値があり、次に入った再帰手順を下図に示すように再帰の過程で理解することもできる.
上記の例では、sqlを以下のように準備します.
if exists (
select *
from sys.tables
where name = 't_tree'
)
drop table dbo.t_tree;
go
create table t_tree
(
id int not null
,node_name varchar(50) null
,parent_id int not null
,[description] varchar(255) null
)
go
insert into t_tree
(
id
,node_name
,parent_id
)
values
(
1
,' '
,0
),
(2 ,' ' ,1),
(3 ,' ' ,1),
(4 ,' ' ,2),
(5 ,' ' ,2),
(6 ,' ' ,2)
go再帰的なプロシージャは、union allを使用してクエリー結果セットをマージするプロシージャです.つまり、次の再帰式に相当します.
resultset(n) = resultset(n-1) union all current_resultset
ここでresultset(n)は最終結果セットを表し、resultset(n-1)は最後から2番目の結果セットを表し、current_resultsetは現在検出された結果セットを表し、最初に「遼寧省」を検索した記録セットは再帰的な初期条件に相当する.再帰的な終了条件はcurrent_ですresultsetが空です.次は、この再帰プロセスの疑似コードです.
public resultset getResultSet(resultset)
{
if(resultset is null)
{
current_resultset = （ ）
id
getResultSet(current_resultset)
}
current_resultset = id id
if(current_result is null) return resultset
id
return getResultSet(resultset union all current_resultset)
}
//
resultset = getResultSet(null) 以上の過程から,この再帰過程は比較的複雑に実現されているが,CTEはこの過程を簡略化するための簡単な構文を提供していることがわかる.
再帰を実現するCTE構文は次のとおりです.
[ WITH [ ,n ] ]
::=
expression_name [ ( column_name [ ,n ] ) ]
AS (
CTE_query_definition1 -- （ ）
union all
CTE_query_definition2 --
) 三、CTE使用例
1.クエリー・ノードおよびその下のすべてのサブノード
with
district as
(
-- ，
select * from t_tree where node_name= N' '
union all
-- select id parent_id
-- ， district ， select
-- null， ，
-- ； 。 district 。
select a.* from t_tree a, district b
where a.parent_id = b.id
)
select * from district 2.サブノードがあるノードを問い合わせる
with
district as
(
select * from t_tree where node_name= N' '
union all
select a.* from t_tree a, district b
where a.parent_id = b.id
),
district1 as
(
select a.* from district a where a.id in (select parent_id from district)
)
select * from district1 注:「遼寧省」と「瀋陽市」だけがサブノードを持っている.
3.MAXRECURSIONを使用して不合理な再帰を防止する
;with district as (
select * from t_tree where node_name= N' '
union all
select a.* from t_tree a, district b
where a.id = b.id --
)
select * from district
OPTION (MAXRECURSION 5) -- ， 4.with asとinsertを組み合わせて使用
if exists (
select *
from sys.objects
where name = 'fn_Test'
and type in ('FN' ,'TF')
)
drop function dbo.fn_Test;
go
create function dbo.fn_Test
(
@nodename varchar(50)
)
returns @result table (
id int not null
,node_name varchar(50) null
,parent_id int not null
,[description] varchar(255) null
)
as
begin
;with district as (
select *
from t_tree where node_name = @nodename
union all
select a.*
from t_tree a, district b
where a.parent_id = b.id
),
district1 as (
select *
from district a
where a.id in (select parent_id
from district)
)
insert into @result
(
id
,node_name
,parent_id
,[description]
)
select id
,node_name
,parent_id
,[description]
from district1;
return ;
end;
go四、再帰CTE使用上の注意事項
再帰CTEの定義と使用には、次の点に注意してください.
1.再帰CTE定義には、少なくとも2つのCTEクエリー定義、1つの位置決めポイントメンバー、および1つの再帰メンバーが含まれる必要があります.複数の位置決めポイントメンバーと再帰メンバーを定義できます.ただし、すべてのロケーションポイントメンバークエリー定義は、最初の再帰メンバー定義の前に配置する必要があります.すべてのCTEクエリー定義は、ロケーションポイントメンバーですが、CTE自体を参照する場合を除きます.
2.位置決めポイントメンバーは、UNIOALL、UNION、INTERSECT、EXCEPTのいずれかの演算子と組み合わせて使用する必要があります.最後のアンカーポイントメンバーと最初の再帰メンバーの間、および複数の再帰メンバーを結合する場合は、UNIOALLコレクション演算子のみが使用されます.
3.ポイント・メンバーと再帰メンバーの列数は一致する必要があります.
4.再帰メンバー内のカラムのデータ型は、アンカーポイントメンバー内の対応するカラムのデータ型と一致する必要があります.
5.再帰メンバーのFROM句は一度だけCTE expression_を参照できます.name.
6.再帰メンバーのCTE_query_definitionでは、次の項目は許可されていません.
(1)SELECT DISTINCT
(2)GROUP BY
(3)HAVING
(4)スカラー重合
(5)TOP
(6)LEFT、RIGHT、OUTER JOIN(INNER JOINの出現を許可)
(7)サブクエリ
(8)CTE_への適用query_definitionのCTEの再帰的参照のヒント.
7.参加するSELECT文が返す列の空性にかかわらず、再帰CTEが返すすべての列は空欄とすることができる.
8.再帰CTEの組み合わせが正しくない場合、無限ループを引き起こす可能性がある.たとえば、再帰メンバー・クエリー定義が親カラムと子カラムに同じ値を返す場合、無限ループが発生します.MAXRECURSIONプロンプトおよびINSERT、UPDATE、DELETE、またはSELECT文のOPTION句のうちの1つの0から32767までの値を使用して、特定の文で許容される再帰級数を制限し、無限ループが発生しないようにすることができます.これにより,ループを生成するコード問題を解決する前に文の実行を制御することができる.サーバ範囲のデフォルト値は100です.0を指定した場合、制限はありません.文ごとに1つのMAXRECURSION値しか指定できません.
9.再帰共通テーブル式を含むビューを使用してデータを更新することはできません.
10.CTEを使用して、クエリ上でカーソルを定義できます.再帰CTEでは、クイック・イン・カーソルと静的(スナップショット)カーソルのみを使用できます.再帰CTEで他のカーソルタイプが指定されている場合、このタイプは静的カーソルタイプに変換されます.
11.CTEでリモート・サーバのテーブルを参照できます.CTEの再帰メンバーでリモート・サーバが参照されている場合、リモート・テーブルごとにスプールが作成され、ローカルでこれらのテーブルに繰り返しアクセスできます.
ここでは、SQL Serverでwith asの使用について説明します.