SQL Serverで階層問合せ

2019-10-06SQLServer

レコードに自身のコードと親コードを持ち、親子関係よりツリー構造を抽出するものをOracleでは階層問合せといい、START WITH句、CONNECT BY句を使用して抽出できますが、SQL ServerにはSTART WITH句、CONNECT BY句が使用できません。
そこでSQL Serverで階層問合せをするSQLを紹介します。

コーディング例

BEGIN
  -- 一時テーブル作成
  CREATE TABLE #所属(
     所属コード		VARCHAR(3)
    ,所属名			VARCHAR(10)
    ,親所属コード	VARCHAR(3)
  );

  -- 一時テーブルへデータ追加
  INSERT INTO
    #所属
  VALUES 
     ('100','A会社','')
    ,('200','B部','100')
    ,('300','C課','200')
    ,('400','D部','100')
    ,('500','E課','400')
  ;

  -- 階層問合せ
  WITH
    TBL AS (
      SELECT
         T1.所属コード
        ,T1.所属名
        ,CAST(T1.所属コード AS VARCHAR(100)) AS 所属コードリスト
        ,CAST(T1.所属名     AS VARCHAR(100)) AS 所属名リスト
      FROM
        #所属 T1
      WHERE
        T1.所属コード = '100'
      UNION ALL
      SELECT
         T2.所属コード
        ,T2.所属名
        ,CAST(TBL.所属コードリスト + ' > ' + T2.所属コード AS VARCHAR(100))
        ,CAST(TBL.所属名リスト     + ' > ' + T2.所属名     AS VARCHAR(100))
      FROM
        #所属 T2
      INNER JOIN
        TBL
        ON T2.親所属コード = TBL.所属コード
    )
  SELECT
     所属コードリスト
    ,所属名リスト
  FROM
    TBL
  ORDER BY
    所属コードリスト
  ;

  -- 一時テーブルの削除
  DROP TABLE #所属;
END

実行結果

所属コードリスト 所属名リスト
100 A会社
100 > 200 A会社 > B部
100 > 200 > 300 A会社 > B部 > C課
100 > 400 A会社 > D部
100 > 400 > 500 A会社 > D部 > E課

WITH句内でUNION ALLを使用すると自身の結果を参照することが可能になります。
以上、「SQL Serverで階層問合せ」でした。

 

株式会社システムトラスト

人材募集中です。

システムトラストでは、システムエンジニア、プログラマーなどを随時募集中です。気軽にご相談ください。

お問合せ