共通テーブル式の使用
共通テーブル式 (CTE) は、単一の SELECT、INSERT、UPDATE、DELETE、CREATE VIEW の各ステートメントの実行スコープ内で定義される一時結果セットと考えることができます。CTE は、オブジェクトとして格納されず、クエリが実行されている間しか保持されない点で、派生テーブルに似ています。派生テーブルと異なるのは、CTE では自己参照が可能であり、同じクエリ内で複数回参照が可能なことです。
CTE は次の目的に使用します。
- 再帰クエリの作成。詳細については、「共通テーブル式を使用する再帰クエリ」を参照してください。
- 通常の用法でビューを使用する必要がない場合、つまり、メタデータにビューの定義を保存する必要がない場合のビューの代用。
- スカラ サブセレクトから派生される列、または非決定的であるか外部からアクセスされる関数による、グループ化の実現。
- 同じステートメント内での結果テーブルの複数回の参照。
CTE を使用すると、複雑なクエリが読みやすくなり、メンテナンスが容易になります。クエリは、独立した、複数の論理的な構成ブロックに分割できます。このような単純なブロックを組み合わせて、より複雑な中間処理用の CTE を作成し、最終的な結果セットを生成することができます。
CTE は、関数やストアド プロシージャ、トリガ、ビューなどのユーザー定義ルーチン内に定義できます。
CTE の構造
CTE は、CTE を表す式名、省略可能な列リスト、および CTE を定義するクエリで構成されます。CTE を定義すると、テーブルやビューと同様に、SELECT、INSERT、UPDATE、または DELETE の各ステートメントで参照できます。CTE は、CREATE VIEW ステートメントを定義する SELECT ステートメントの一部としても使用できます。
CTE 構文の基本構造は次のとおりです。
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
列名リストは、クエリ定義ですべての結果列の名前が指定されている場合にのみ、省略できます。
CTE を実行するステートメントは、次のようになります。
SELECT <column_list>
FROM expression_name
例
次の例では、CTE 構造のコンポーネント (式名、列リスト、およびクエリ) を示します。CTE 式 Sales_CTE には 3 つの列 (SalesPersonID、NumberOfOrders、および MaxDate) があり、販売員ごとに SalesOrderHeader テーブルから受注総数と最後に受注した日付を格納するように定義されています。このステートメントを実行した場合、CTE は 2 回参照されます。1 回目は各販売員の列を選択して返すときで、2 回目は販売員の上司について同様の詳細情報を取得するときです。販売員のデータと上司のデータは、どちらも 1 行で返されます。
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
次に結果セットの一部を示します。
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268 48 2004-06-01 273 NULL NULL
275 450 2004-06-01 268 48 2004-06-01
276 418 2004-06-01 268 48 2004-06-01
277 473 2004-06-01 268 48 2004-06-01
参照
その他の技術情報
WITH common_table_expression (Transact-SQL)
SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
CREATE VIEW (Transact-SQL)