新着情報

Home > 開発者ブログ > intra-mart > SQLServerの共通テーブル式(CTE)を使用した再帰SQLでパスなどの階層情報を組み立てる(フォルダパス、パンくず)

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を工夫する必要があります。

 

2017年05月19日(金)

テレマーケティングシステムsakuraトライアル

コールセンターシステム sakuraCTI

資料請求

お問合わせ

コラム

用語集

開発者ブログ

イントラマート

Sakuraテレマーケティングシステム

 

 

営業リスト

インフラソリューションのご案内

導入事例

テレアポノウハウ本プレゼントキャンペーン

認証マーク

アースリンクは
プライバシーマークを
取得しています。

プライバシーポリシー


SSLとは?

当サイトでは、実在性の証明とプライバシー 保護のためSSLサーバ証明書を使用しSSL 暗号化通信を実現しています。

このページの先頭へ