次の方法で共有


共通テーブル式の使用

共通テーブル式 (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 つの列 (SalesPersonIDNumberOfOrders、および 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)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手