with使った再帰結合

sql SQLServer

今更ながらまともに長いSQLを久しぶりに見たので、結合周り再入門的な。

WITH
     lavels(id, name, level, path) AS
     (SELECT categories.id, categories.name, 1, CONVERT(varchar(255),categories.name)
         FROM categories
         WHERE categories.parent_id = 0
      UNION ALL
      SELECT categories.id, categories.name, parent.level + 1,
             CONVERT(varchar(255),parent.path + ' > ' + categories.name)
         FROM categories
             INNER JOIN lavels parent
                 ON categories.parent_id = parent.id
         WHERE categories.deleted_flag = 0 AND categories.display_order <> 0
     )
         
select *
from lavels

[categoriesテーブル]

id name parent_id
1 大カテゴリ1 0
2 大カテゴリ2 0
3 中カテゴリ1 1
4 中カテゴリ2 2
5 小カテゴリ1 4

結果

id name level item_path
1 大カテゴリ1 1 大カテゴリ1
2 大カテゴリ2 1 大カテゴリ2
3 中カテゴリ1 2 大カテゴリ1 > 中カテゴリ1
4 中カテゴリ2 2 大カテゴリ2 > 中カテゴリ2
5 小カテゴリ1 3 大カテゴリ2 > 中カテゴリ2 > 小カテゴリ1

最初これでlevelとitem_pathが増えてってるのが不思議だった。
けどunion後半でlavelsの行どんどん増えてって、それ見て結合してるんだからそりゃ増えてくよね、と。
今までシンプルなSQLで何とかなっちゃっていたので再入門。
確かに一番最初SQLやったときにこんなの色々やった覚えある。