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

一人アドカレ

日	ポスト
1	一時テーブルが存在していたら削除する
2	何度も使う中間テーブルに名前をつけてクエリを見やすくする
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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

この記事は自身のブログを書き直したものです。

課題

クエリを書いていると、何度も同じ中間テーブルを書いている時がある。自分の場合、テーブル結合を幾つかしている時に多い。
同じ中間テーブルのクエリを何度も書いているとミスの元だし、修正するにも手間だし見難いしで大変なので、共通テーブル式を使い見やすくする。

準備

実行環境

• SQL Server 2017 Standard
  • 2008 R2 Express, 2012 Standardでも確認済み
• Management Studio 2017

サンプルデータ

fruitsテーブル

ID	Name	Price
1	apple	100
2	apple	120
3	orange	50
4	orange	40
5	apple	80
6	orange	60
7	banana	120
8	banana	150

解法

共通テーブル式とは名前をつけた中間テーブルのこと。WITH句を使うことで実現できる。

WITH [commonTableName] AS (
     SELECT [hoge] AS [column1]
          , [fuga] AS [column2]
       FROM [someTbl]
      WHERE [someCol] = 'piyo' )
SELECT [column1]
     , [column2]
  FROM [commonTableName]

WITH句からはじまり、WITHの後には共通テーブルの名前[commonTableName]が続く。
その後の括弧内のSELECT文は共通テーブルの定義になり、この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]
 INNER 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

感想

この例だとぜんぜん共通テーブル式の便利なところが伝わらないかな。。。
同じクエリを何回も書くような時使うと、すっきりして見やすいクエリになります。
再帰クエリも便利だし記録しておきたい。

共通テーブルは中間テーブルである、というと一時テーブルとの違いがわからないので別途記事を書きたい。
⇒『共通テーブル式と一時テーブルの参照可能な範囲の違い』を書きました。

参考

• 共通テーブル式の使用