• [アーティクル]

共通テーブル式の使用

共通テーブル式 (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、SalesOrderID、および OrderDate) があり、販売員ごとに年間の受注総数を格納するように定義されています。

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

次に結果セットの一部を示します。

SalesPersonID TotalSales  SalesYear

------------- ----------- -----------

274           4           2001

274           20          2002

274           14          2003

274           10          2004

275           56          2001

275           139         2002

275           169         2003