SQL Serverで階層問合せ
レコードに自身のコードと親コードを持ち、親子関係よりツリー構造を抽出するものを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','Ａ会社','')
,('200','Ｂ部','100')
,('300','Ｃ課','200')
,('400','Ｄ部','100')
,('500','Ｅ課','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 | Ａ会社 |
| 100 > 200 | Ａ会社 > Ｂ部 |
| 100 > 200 > 300 | Ａ会社 > Ｂ部 > Ｃ課 |
| 100 > 400 | Ａ会社 > Ｄ部 |
| 100 > 400 > 500 | Ａ会社 > Ｄ部 > Ｅ課 |
WITH句内でUNION ALLを使用すると自身の結果を参照することが可能になります。
以上、「SQL Serverで階層問合せ」でした。