何度も使う中間テーブルに名前をつけてクエリを見やすくする

課題

クエリを書いていると、何度も同じ中間テーブルを書いている時がある。自分の場合、テーブル結合を幾つかしている時に多い。

同じ中間テーブルのクエリを何度も書いているとミスの元だし修正するにも手間だし見難いしで大変なので、共通テーブル式を使い見やすくする。

実行

実行環境はSQL Server 2008 R2 Express + Management Studio.

使うサンプルデータは前回でも使った自作のfruitsテーブル。

共通テーブル式とは、名前をつけた中間テーブルのこと。自己参照も可能で、これを使うと再帰クエリが書ける。

共通テーブル式はWITH句を使うことで実現できる。

WITH [commonTableName] AS (
  SELECT文
)
SELECT
    [column1]
  , [column2]
  , ...

• [commonTableName]には共通テーブルの名前
• SELECT文は共通テーブルの定義。例えば、何度も書きたくない中間テーブルの抽出文

共通テーブル式は上の例のようにSELECT文の前に書いて使う。

fruitsサンプルテーブルを使った例が以下。共通テーブル式に果物ごとの最安値を抽出する文を指定して、SELECT文で各レコードごとに一致する果物の値段と最安値の差額を抽出した。

WITH [commonTable] AS (
  SELECT
      [Name]
    , MIN( [Price] ) AS [minPrice]
  FROM
    [fruits]
  GROUP BY
    [Name]
)
SELECT
    [fruits].[ID]
  , [fruits].[Name]
  , [fruits].[Price] - [commonTable].[minPrice] AS [diffPrice]
FROM
  [fruits]
JOIN
  [commonTable]
ON
  [commonTable].[Name] = [fruits].[Name]
ORDER BY
  [ID];

ID	Name	diffPrice
1	apple	20
2	apple	40
3	orange	10
4	orange	0
5	apple	0
6	orange	20
7	banana	30
8	banana	60
9	cherry	0
10	cherry	100
11	pineapple	0
12	pineapple	30
13	banana	0

評価

この例だとぜんぜん共通テーブル式の便利なところが伝わらないのでもっと良い例を作ろう。

再帰クエリも便利だし記録しておきたい。

参考

• 共通テーブル式の使用