再帰的SQL を CommonTableExpression (CTE) を使って実現する

TechEd 2008 NorthAmerica で聞いてきたことのメモ。

ちなみにTechEdにおけるセッションのタイトルは T-SQL Set Based Programming だった。まずはじめに言われたのが「cursor 使うな!」

内容のメモを書こうと思ったが Recursive Queries Using Common Table Expressions (MSDN) に書いてある内容そのままなので必要ならこちらの日本語訳を見る方がいいかもしれない。

書き方の概要

WITH CTEテーブル名
AS
(
    最初の要素のSELECT文
  UNION ALL
    再帰を表すjoinを含んだSELECT文(SELECT ... FROM 対象テーブル INNER JOIN CTEテーブル名 ON CTEテーブル名.親ID=対象テーブル.子ID)
)

OPTION(MAXRECURSION, 2) などとヒントを渡すと再帰レベルの最大値を指定できる。

CTEテーブル名の定義のところに、フィールド名として level を作成しておき、再帰を表すjoinのところで、level+1 を level に割り当たるようにしておくと、再帰レベルを取得できるようになる。

広告