CTE 共通テーブル式⇒再帰的な問い合わせを行う。

この記事を読むのに必要な時間は約 8 分です。

CTE 再起問い合わせ
機械的に！
1. ほぼ、覚書です。

CTE 再起問い合わせ

まずは言い訳を

管理人はCTEの再帰問い合わせが非常に苦手です。

CTEの説明を見ても正直よくわかりません。※機械的に覚えています。

SQLのBlogを書いている手前、説明しないわけにはいきませんので頑張って説明します！！

CTE再帰問い合わせ

CTEを副問い合わせの代わりのように使う方法は簡単且つ、非常に有用な手法です。

可読性が非常に高くなりますので管理人は積極的に利用しております。

ただ、今回説明するCTE再帰問い合わせは、ぶっちゃけ覚えなくても良いかなと思っています。理由は

使う事が無いからです！

もしかしたら、意図的にこのようなデータを作る場合もあるかもしれませんが、今までの経験上、仕事でこのようなデータに出会ったことが無いからです。

[speech_bubble type=”drop” subtype=”L1″ icon=”02ukiyoe/tomi_R.png” name=”とみさん”]言い訳がくどい！[/speech_bubble]

では、説明します。

データベースのテーブル内に、自レコードの親レコードをポインタとして格納しているようなテーブルで、親を辿っていくような場合に利用します。

AdventureWorksDW2016_EXTにもこの様なサンプルテーブルがありますので見てみましょう。

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT [OrganizationKey]
      ,[ParentOrganizationKey]
      ,[OrganizationName]
  FROM [AdventureWorksDW2016_EXT].[dbo].[DimOrganization]

テーブルがこの様になっています。

このOrganizationNameの情報に対してParentOrganizationKeyをたよりに親をどんどん辿っていきます。

SQLを書いてみる。

以下のような書き方になります。

機械的に覚えていますので、説明はできません！！！

with cte as(
SELECT [OrganizationKey]
      ,[ParentOrganizationKey]
      ,cast([OrganizationName] as varchar(100)) as OrganizationName
	  ,1 as lv
  FROM [DimOrganization] where [ParentOrganizationKey] is  null
  union all
SELECT [DimOrganization].[OrganizationKey]
      ,[DimOrganization].[ParentOrganizationKey]
      ,cast(CTE.[OrganizationName]+' / '+[DimOrganization].[OrganizationName] as varchar(100)) as OrganizationName
	  ,1+lv as lv
  FROM [DimOrganization],CTE
  where [DimOrganization].ParentOrganizationKey=CTE.OrganizationKey
   )
  select * from cte order by OrganizationName,lv

はい！　OrganizationNameがきれいに親を辿っているのが分かります。

ちょっとこの図だとわかりにくいのでエクセルで色付けしてみました。

一番左側のOrganizationKeyは単純に自身のレコード情報です。

次のParentOrganizationKeyが親を辿っています。

※上記エクセル図の色付けで対応付けています。

最後に、一番右側の列ですが、階層の深さを表現しています。

機械的に！

ほぼ、覚書です。

自分の覚書のつもりで書いています！

++++++++++++++++++++++++++++++++++++++

with cte as(
SELECT

[OrganizationKey]
,[ParentOrganizationKey]
,cast([OrganizationName] as varchar(100)) as OrganizationName
,1 as lv
FROM [DimOrganization] where [ParentOrganizationKey] is null
union all
SELECT

[DimOrganization].[OrganizationKey]
,[DimOrganization].[ParentOrganizationKey]
,cast(CTE.[OrganizationName]+’ / ‘+[DimOrganization].[OrganizationName] as varchar(100)) as OrganizationName
,1+lv as lv
FROM [DimOrganization],CTE
where [DimOrganization].ParentOrganizationKey=CTE.OrganizationKey
)
select * from cte order by OrganizationName,lv

++++++++++++++++++++++++++++++++++++++

元のSQL文をunion All で挟む形で上側と下側に分けて書く
上側の文にWhere区で、親ポインタが無いデータのみ表示されるよう、where区で is null を付け足す。
文字列を連結する場所についてはCASTやFormatで上と下のSQL文の形式をそろえる。
下側の文で表示させる列名は上側のテーブル名を指定する
テーブルを結びつけるタイミングで、初めて共通テーブル名を使う
上側のSQL文親ポインタと下側SQL文自ポインタをWhere区(or InnerJoin)で結ぶ
ここまで終わって最後に、親を辿りたいテーブルで共通テーブル側の列を出す

非常に雑ですが、この順番通りに書いていけばCTE再帰問い合わせができます。

※3で、CAST等をしておかないと以下のエラーがでます。

アンカーの型と再帰部分の型が一致していません。

CTE再帰問い合わせの、わかりやすい説明をしているページがありましたら誰か教えてください。