2017年5月19日

SQLServerの共通テーブル式（CTE）を使用した再帰SQLでパスなどの階層情報を組み立てる（フォルダパス、パンくず）

 

 

～～　SQLServerの再帰クエリ(CTE)を利用し、カラムデータを再帰的に取得する方法について　～～

 

例えば、以下の様な1カラムのデータを連結したレコードを1度のクエリ発行で取得する方法について紹介します。

 

             アースリンク／管理部／2016年度_資料

 

【データ準備】

●テーブル情報

　テーブルに以下のレコードを作成します。

id	parentId	name
1	​(NULL)​	アースリンク
2	1	管理部
3	1	BS部
4	2	2013年度_資料
5	2	2014年度_資料
6	2	2015年度_資料
7	2	2013年度_資料
8	3	2014年度_資料
9	3	2015年度_資料
10	3	2013年度_資料
11	3	2014年度_資料

 

●階層情報の「パス」を組み立てる再帰SQL

WITH cte_path(id, parentId, layerLevel, path) AS (
  SELECT
    id,
    parentId,
    1 AS layer_level,
    CAST(name AS nvarchar(MAX)) AS path
  FROM
    test_table
  WHERE
    parentId IS NULL
  UNION ALL
  SELECT
    test.id AS id,
    test.parentId AS oya_id,
    cte.layerLevel + 1 AS layerLevel,
    cte.path + N'/' + test.name AS path
  FROM
    test_table test
    INNER JOIN cte_path cte
      ON test.parentId = cte.id
)
SELECT
  *
FROM
  cte_path

●実行結果

id	parentId	layerLevel	path
1	​(NULL)​	1	アースリンク
2	1	2	アースリンク/管理部
3	1	2	アースリンク/BS部
8	3	3	アースリンク/BS部/2014年度_資料
9	3	3	アースリンク/BS部/2015年度_資料
10	3	3	アースリンク/BS部/2013年度_資料
11	3	3	アースリンク/BS部/2014年度_資料
4	2	3	アースリンク/管理部/2013年度_資料
5	2	3	アースリンク/管理部/2014年度_資料
6	2	3	アースリンク/管理部/2015年度_資料
7	2	3	アースリンク/管理部/2013年度_資料

 

 

 

 

 

 

 

 

 

 

 

 

 

 

これは、共通テーブル式（CTE）を用いて再帰的にSQLを実行する事により得られる結果となります。

 

更に、「管理部」で絞り込みたい場合、

 

●以下の様にキー情報を追加します（busho_cd)

id	parentId	name	busho_cd
1	​(NULL)​	アースリンク	​(NULL)​
2	1	管理部	001
3	1	BS部	002
4	2	2013年度_資料	001
5	2	2014年度_資料	001
6	2	2015年度_資料	001
7	2	2013年度_資料	002
8	3	2014年度_資料	002
9	3	2015年度_資料	002
10	3	2013年度_資料	002
11	3	2014年度_資料	002

 

●SQLにキー情報のカラムを追加します。

　busho_cdを追加

WITH cte_path(id, parentId, layerLevel, path, busho_cd) AS (
  SELECT
    id,
    parentId,
    1 AS layer_level,
    CAST(name AS nvarchar(MAX)) AS path,
    busho_cd
  FROM
    test_table
  WHERE
    parentId IS NULL
  UNION ALL
  SELECT
    test.id AS id,
    test.parentId AS oya_id,
    cte.layerLevel + 1 AS layerLevel,
    cte.path + N'/' + test.name AS path,
    test.busho_cd
  FROM
    test_table test
    INNER JOIN cte_path cte
      ON test.parentId = cte.id
      where test.busho_cd = '001'
)
SELECT
  *
FROM
  cte_path

 

●実行結果

id	parentId	layerLevel	path	busho_cd
1	​(NULL)​	1	アースリンク	​(NULL)​
2	1	2	アースリンク/管理部	001
4	2	3	アースリンク/管理部/2013年度_資料	001
5	2	3	アースリンク/管理部/2014年度_資料	001
6	2	3	アースリンク/管理部/2015年度_資料	001

 

 

 

注意点としては、階層を結合する際に使用するキー情報（Id,parentId）で絞り込んでしまうと

上手く階層が作成されなくなってしまいます。

 

Java等でSQL実行結果を再帰的に処理して同様の結果を得ようとすると意外に大変かと思われます。

但し、それはパフォーマンスやレコード数が少ない場合に限られます。

レコード数が数千を超えたり、ある特殊な条件で階層の組み立てを絞り込みたい場合は、上記SQLを工夫する必要があります。