共通式って使ってる？？

共通式とは

サブクエリを定義し、あたかもテーブルかの様に仕様できる記述方法
※以下のクエリ及び解説は全てSqlServerを想定して記述しています

-- 注意事項：
-- 共通式の宣言の前に処理がある場合、必ず";"で区切られていること
-- ";"抜きだとエラー

-- 共通式の宣言 WITH 任意の共通式名 AS
WITH CTE_1 AS
(
    -- サブクエリの内容を記述
    SELECT ID FROM T_HOGE(NOLOCK)
    WHERE ID = 1
)
-- ","区切りで複数定義可能
, CTE_2 AS
(
    SELECT ID FROM T_HOGEHOGE(NOLOCK)
    WHERE ID = 2
)
SELECT * FROM T_HUGA (NOLOCK) AS HUGA
LEFT JOIN CTE_1 (NOLOCK) AS CTE
ON 
    HUGA.ID = CTE_1.ID
LEFT JOIN CTE_2 (NOLOCK) AS CTE
ON
    HUGA.ID = CTE_2.ID

一時テーブルとの違い

• 宣言直後のクエリでのみ参照可能
  • @テーブルの場合：宣言後、一連のクエリの処理が完了するまで参照可能
  • #テーブルの場合：宣言後、明示的にテーブルを削除するか、クエリセッションが切れるまで参照可能
• あくまでサブクエリの外だしなので、メモリ上で処理
  • @テーブルの場合、同じくメモリ上で処理
    • 注意事項：メモリで処理しきれない場合、実ファイルへの書き込みが発生
  • #テーブルの場合、実ファイルへ書き込み、実ファイル上から処理
• インデックスは付けられない（サブクエリなので）
  • @#テーブルの場合、インデックスを作成可能

上記の特徴から見た使えるケース

以下のケースで有効

• 一回限りの使用
  • 同クエリの内部で何度も使用するのであれば、一時テーブルした方が良い
• 使用する件数が少ないこと
  • 処理できる件数を制限する工夫が必要
• サブクエリが乱立して見にくい場合
  • 共通式として外出しする
  • 一時テーブルにするほどじゃない場合は有効

応用編

再帰処理

共通式は 自己参照ができる!!
以下のクエリは、自己参照を行い、条件に合致するまで結合を繰り返す

-- 共通式(CTE)
-- 基本：サブクエリの外出し
-- 応用：下記のような再帰的処理(自己参照)

-- 今日から30日前までの日付を取得するクエリ
WITH DAYS AS 
(
  SELECT 
      CURRENT_TIMESTAMP AS YMD
  UNION ALL
  SELECT 
      DATEADD(Y, -1, YMD)
  FROM DAYS -- ここが再帰的(自己を参照している)
  WHERE 
      DAYS.YMD >= DATEADD(y, -30, CURRENT_TIMESTAMP)
      -- 30日前まで遡って自己結合を繰り返す 
)
SELECT * FROM DAYS