[SQL Server]WITH句で共通テーブル（CTE式）を使用する

[スポンサードリンク]

共通テーブル（CTE式）の使い方

共通テーブルを使うメリット

ソースの可読性を向上する

同じテーブルの副問い合わせや、同じテーブルの結合など。
SQLの冗長化を防ぎ、ソースを見やすくすることができる。

テーブルの結合前に行数を減らしておくことができる

行数の多いテーブル同士を結合すると、結合条件式の処理にも時間がかかり、クエリのパフォーマンスも低下する。
共通テーブル式（CTE式）の内部で条件を絞り込めば、行数を減らすことができ、クエリのパフォーマンスも向上する。

共通テーブル式の記述方法

共通テーブル式は下記の通り、WITH句を用いて記述する。

-- パラメータ（検索条件）
DECLARE
     @PlantCode varchar(max) = 'Tokyo'
    ,@DateFrom datetime = '2017/01/01'
    ,@DateTo datetime = '2017/01/07'
;
-- 共通テーブル式（CTE式）ここから
-- 生産情報テーブルを工場と日付で絞り込み
WITH SEISAN AS (
    SELECT
         PLANT_CODE              -- 工場コード
        ,SEISAN_DATE             -- 生産日付
        ,ITEM_CODE               -- 製品コード
        ,SUM(SEISAN_CNT) AS CNT  -- 生産数
    FROM
        SEISAN_TABLE             -- 生産情報
    WHERE
        PLANT_CODE = @PlantCode
    AND SEISAN_DATE BETWEEN @DateFrom AND @DateTo

    GROUP BY
         PLANT_CODE
        ,SEISAN_DATE
        ,ITEM_CODE
)
-- 製品マスタを製品取り扱い生産工場で絞り込み
, ITEM_LIST AS (
    SELECT
         ITEM_CODE  -- 製品コード
        ,ITEM_NAME  -- 製品名
    FROM
        ITEM_MST
    WHERE
        PLANT_CD = @PlantCode
)
-- 共通テーブル式ここまで

SELECT
     S.PLANT_CODE    -- 工場コード
    ,S.SEISAN_DATE   -- 生産日付
    ,S.ITEM_CODE     -- 製品コード
    ,I.ITEM_NAME     -- 製品名
    ,S.CNT           -- 生産数
FROM
    SEISAN S
LEFT OUTER JOIN
    ITEM_LIST I ON
        S.ITEM_CODE = I.ITEM_CODE

-- パラメータ（検索条件）

DECLARE

@PlantCode varchar(max) = 'Tokyo'

,@DateFrom datetime = '2017/01/01'

,@DateTo datetime = '2017/01/07'

;

-- 共通テーブル式（CTE式）ここから

-- 生産情報テーブルを工場と日付で絞り込み

WITH SEISAN AS (

SELECT

PLANT_CODE -- 工場コード

,SEISAN_DATE -- 生産日付

,ITEM_CODE -- 製品コード

,SUM(SEISAN_CNT) AS CNT -- 生産数

FROM

SEISAN_TABLE -- 生産情報

WHERE

PLANT_CODE = @PlantCode

AND SEISAN_DATE BETWEEN @DateFrom AND @DateTo

GROUP BY

PLANT_CODE

,SEISAN_DATE

,ITEM_CODE

)

-- 製品マスタを製品取り扱い生産工場で絞り込み

, ITEM_LIST AS (

SELECT

ITEM_CODE -- 製品コード

,ITEM_NAME -- 製品名

FROM

ITEM_MST

WHERE

PLANT_CD = @PlantCode

)

-- 共通テーブル式ここまで

SELECT

S.PLANT_CODE -- 工場コード

,S.SEISAN_DATE -- 生産日付

,S.ITEM_CODE -- 製品コード

,I.ITEM_NAME -- 製品名

,S.CNT -- 生産数

FROM

SEISAN S

LEFT OUTER JOIN

ITEM_LIST I ON

S.ITEM_CODE = I.ITEM_CODE

CTE式の別の書き方

列名を省略した書き方

上記のCTE式の書き方は、列名を省略した書き方である。

-- よく使われる構文
WITH CTE式名 AS (
    
    --SQL文

)

-- よく使われる構文

WITH CTE式名 AS (

--SQL文

)

WITH 売上 AS (
    SELECT
        ID,
        ITEM_CD,
        ITEM_NM,
        QTY
    FROM
        ZZ_TEST
)

SELECT
    *
FROM
    売上

WITH 売上 AS (

SELECT

ID,

ITEM_CD,

ITEM_NM,

QTY

FROM

ZZ_TEST

)

SELECT

FROM

売上

列名を省略しない書き方

CTE式は、列名を省略しない書き方もできる。

-- 列定義を行っている構文
-- 列定義は省略できるし、SQL文のなかでAS句を
-- 使えばいいため、これはあまり書かない
WITH CTE式名 (列名１,列名２,・・・) AS (
    
    --SQL文
)

-- 列定義を行っている構文

-- 列定義は省略できるし、SQL文のなかでAS句を

-- 使えばいいため、これはあまり書かない

WITH CTE式名 (列名１,列名２,・・・) AS (

--SQL文

)

このような書き方

WITH 売上 (ID,商品CD,商品名,数量) AS (
    SELECT
        ID,
        ITEM_CD,
        ITEM_NM,
        QTY
    FROM
        ZZ_TEST
)

SELECT
    *
FROM
    売上

WITH 売上 (ID,商品CD,商品名,数量) AS (

SELECT

ID,

ITEM_CD,

ITEM_NM,

QTY

FROM

ZZ_TEST

)

SELECT

FROM

売上

しかし、これはぱっと見分かりにくいし、下記ソースのように選択リストの中でAS句を使用した方が分かりやすいため、このようなCTE式の定義はなかなかしない。

また、定義した列の数とSELECT～FROM内（選択リスト）の数が異なればエラーとなる。

WITH 売上 AS (
    SELECT
        ID          AS ID,
        ITEM_CD     AS 商品CD,
        ITEM_NM     AS 商品名,
        QTY         AS 数量
    FROM
        ZZ_TEST
)

SELECT
    *
FROM
    売上

WITH 売上 AS (

SELECT

ID AS ID,

ITEM_CD AS 商品CD,

ITEM_NM AS 商品名,

QTY AS 数量

FROM

ZZ_TEST

)

SELECT

FROM

売上

この二つの結果は、いずれも下図のようになる。

注意点

CTE式とIF文

CTE式の後に、例えば「SELECT」文などを書くのはエラーにはならないが、CTE式の定義の後にIF文を書くとエラーになる。
例えば「キーワード ‘IF’ 付近に不適切な構文があります。」というエラーが出る。
IF文で条件分岐を書きたいときにはCTE式は使わず、テーブル型変数か一時テーブルを使う方向で考えるとよい。
または、CASE WHENでフラグ列を作って、WHERE で何とかできれば、その方向で回避することも考えるとよい。

CTE式の内容が実行するたびに変わる

これは、同じCTE式を使用している複数の箇所で、実行結果が異なるという場合。
例えば、CTE式の中ではORDER BYは使えないが、CTE式をSELECTする度にデータの並び順が変わるという現象。
これはめったに起きないが、別のデータベースのテーブルをリンクサーバー経由で取得する場合、そのようなおかしな現象が発生する場合があった。

それで都合が悪い場合は（ORDER BYが使えない場所でさらに並び順が変わってはいけない処理）、テーブル型変数か一時テーブルを使うことも検討するとよい。

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい