SQLSERVER 2013を使い階層テーブルの再帰ツリーを表示しようとしているのですが上手くいきません。

Question

実現したいこと

ここに実現したいことを箇条書きで書いてください。

SQLSERVER 2013を使い階層テーブルの再帰ツリーを表示
リンク内容

発生している問題・エラーメッセージ

取得内容がおかしい

該当のソースコード

with R as (
     select MASTER,CHILD,SEQ,1 as LVL from CSM070STRC CS070
     where  MASTER = 'test01'
     union all 
     select C.MASTER,C.CHILD,C.SEQ, LVL+1 as RR  
	 from CSM070STRC as C JOIN R ON C.MASTER = R.CHILD
           )
SELECT * FROM R ORDER BY SEQ

試したこと

ここに問題に対して試したことを記載してください。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[neko_the_shadow]

①CSM070STRCのテーブル定義
②CSM070STRCのサンプルデータ
③求める結果
可能な範囲でよいので、↑をそれぞれ提示いただけますか?

また問題になっているのは並び順が正しくないという認識でよいですか?
・現状…SEQの昇順
・求める結果…ツリーを深さ優先探索でたどっていった結果を表示したい

[29507-01]

① master,child,ksu
➁ ｻﾝﾌﾟﾙﾃﾞｰﾀは添付画像の左側
test01,8387
8387,8388
8387,A0752
test01,8426
下は割愛します
③ 添付左の画像
行 --- seq順
LVL---階層
CODE データ上のchild

[29507-01]

添付左の画像が欲しい結果です。方法は構成マスターを順に読み込んで処理しているので
そうではなく,画像右側のsqを使ったの再帰ﾂﾘｰ方法でやりたいのです

[neko_the_shadow]

A. 質問者の方とIDが違うようですが、同一の方が回答されている認識でよいですか?
B. 「➁ ｻﾝﾌﾟﾙﾃﾞｰﾀは添付画像の左側」とありますが、これは「右側」が正しいという認識でよいですか?
C. 「行 --- seq順」とありますが、提示していただいたテーブル定義「master,child,ksu」にはないようです。テーブル定義にはカラムseqもあって、添付画像右側と同じデータが入っているという認識でよいですか?

[neko_the_shadow]

やりたいことがうまく整理できていない印象を受けます。以下のようなデータがCSM070STRCにあるとします。

・(master, chid, seq) = (test01, 8387, 1) → (8387, 8388, 2)
・(master, chid, seq) = (test01, 8426, 2) → (8426, 5049, 3) / (8426, 5050, 8)

これを次のように表示したいという認識でよいですか?

(行, lvl, code)
(1, 1, 8387) ← 親CODEが8387
(2, 2, 8388) ← 親CODEが8387
(3, 1, 8426) ← 親CODEが8426
(4, 2, 5049) ← 親CODEが8426。LVLが同じならseqの昇順
(5, 2, 5050) ← 親CODEが8426。LVLが同じならseqの昇順

[29507-01]

A.質問者です
B.左の画像が表示したい結果です。右の画像はSQLで実行したダメな結果です
SEQは読み込んだ順番です。データにはありません
(master,child)
test01,8387
8387,8388
8387,A0752
test01,8389
test01,8392
8392,8393
8392,A0752
これを
SEQ LVL CODE
1 1 8387 　 LVL はtest01の下の階層
2 2 8388　　LVL は8387の下の階層
3 2 A0752 　LVL は8387の下の階層
4 1 8389 LVL はtest01の下の階層
5 1 8392 LVL はtest01の下の階層
6 2 8393 　 LVL は8392の下の階層
7 2 A0752 LVL は8392の下の階層
としたいのです

[29507-01]

neko_tha_shadow様　2023/10/14 12:28の認識で間違いないです。
lvlは親の直下だと1 その下の階層だと2
seqはデータ上持っていなくて展開した順に行としてセットします

[pentagon]

有難うございます。
shadow様　結果上手くいったのですが表示順だけをtest01直下のseq順にならないでしょうか?
申し訳ございません。
seqはデータに持っていないと言っていたのですが
持っていました。
shadowさんのデータの内容で
・(master, chid, seq) = (test01, 8387, 1) → (8387, 8388, 1)
・(master, chid, seq) = (test01, 8426, 2) → (8426, 5049, 1) / (8426, 5050, 2)
・(master, chid, seq) = (test01, 8392, 3) → (8392, 8393, 1) / (8392, A0752, 2)
test01直下のseq順に出力出来ないでしょうか?

8387
　　8388
A0752
8426
5049
5050　　
8392
8393
A052

[pentagon]

shadow様　すみません
結果のデータでさらに直下のseq順に表示するのは可能なのでしょうか?
(直下でないseqは無視　- - - 書かれているseq自体がおかしい為)

Answer 1

質問の意図を十分に汲み取れているか心配ですが…。まずテーブル定義とサンプルデータは以下の通りです。

CREATE TABLE CSM070STRC (master VARCHAR(6), child VARCHAR(6));
INSERT INTO CSM070STRC VALUES ('test01', '8387' );
INSERT INTO CSM070STRC VALUES ('8387'  , '8388' );
INSERT INTO CSM070STRC VALUES ('8387'  , 'A0752');
INSERT INTO CSM070STRC VALUES ('test01', '8426' );
INSERT INTO CSM070STRC VALUES ('8426'  , '5049' );
INSERT INTO CSM070STRC VALUES ('8426'  , '5050' );
INSERT INTO CSM070STRC VALUES ('8426'  , '4132' );
INSERT INTO CSM070STRC VALUES ('8426'  , '1271' );
INSERT INTO CSM070STRC VALUES ('8426'  , '4248' );
INSERT INTO CSM070STRC VALUES ('test01', '8392' );
INSERT INTO CSM070STRC VALUES ('8392'  , '8393' );
INSERT INTO CSM070STRC VALUES ('8392'  , 'A0752');

次のようなSQLで求める結果を得られると思います。

WITH R (root, code, lvl) AS (
    SELECT child, child, 1 FROM CSM070STRC WHERE master = 'test01'
  UNION ALL
    SELECT root, C.child, lvl+1
    FROM R
    JOIN CSM070STRC C ON R.code = C.master
)
SELECT ROW_NUMBER() OVER (ORDER BY root, lvl, code) seq, lvl, code
FROM R
ORDER BY seq

---

2023-10-05 20:05追記

表示順だけをtest01直下のseq順にならないでしょうか? 申し訳ございません。

seqはデータに持っていないと言っていたのですが 持っていました。

テーブル定義とサンプルデータは次のようになっていたということですかね?

CREATE TABLE CSM070STRC (master VARCHAR(6), child VARCHAR(6), seq INT);
INSERT INTO CSM070STRC VALUES ('test01', '8387' , 1 );
INSERT INTO CSM070STRC VALUES ('8387'  , '8388' , 2 );
INSERT INTO CSM070STRC VALUES ('8387'  , 'A0752', 2 );
INSERT INTO CSM070STRC VALUES ('test01', '8426' , 2 );
INSERT INTO CSM070STRC VALUES ('8426'  , '5049' , 7 );
INSERT INTO CSM070STRC VALUES ('8426'  , '5050' , 8 );
INSERT INTO CSM070STRC VALUES ('8426'  , '4132' , 9 );
INSERT INTO CSM070STRC VALUES ('8426'  , '1271' , 10);
INSERT INTO CSM070STRC VALUES ('8426'  , '4248' , 11);
INSERT INTO CSM070STRC VALUES ('test01', '8392' , 3 );
INSERT INTO CSM070STRC VALUES ('8392'  , '8393' , 6 );
INSERT INTO CSM070STRC VALUES ('8392'  , 'A0752', 7 );

必要な結果を求めるSQLは以下の様になると思います。

WITH R (root_seq, code, seq, lvl) AS (
    SELECT seq, child, seq, 1 FROM CSM070STRC WHERE master = 'test01'
  UNION ALL
    SELECT R.root_seq, C.child, C.seq, R.lvl+1
    FROM R
    JOIN CSM070STRC C ON R.code = C.master
)
SELECT ROW_NUMBER() OVER (ORDER BY root_seq, lvl, seq) 行, lvl, code
FROM R
ORDER BY 行

Comments

[pentagon]

有難うございます。
shadow様　結果上手くいったのですが表示順だけをtest01直下のseq順にならないでしょうか?
申し訳ございません。
seqはデータに持っていないと言っていたのですが
持っていました。
shadowさんのデータの内容で
・(master, chid, seq) = (test01, 8387, 1) → (8387, 8388, 1)
・(master, chid, seq) = (test01, 8426, 2) → (8426, 5049, 1) / (8426, 5050, 2)
・(master, chid, seq) = (test01, 8392, 3) → (8392, 8393, 1) / (8392, A0752, 2)
test01直下のseq順に出力出来ないでしょうか?

8387
　　8388
A0752
8426
5049
5050　　
8392
8393
A052

結果のデータでさらに直下のseq順に表示するのは可能なのでしょうか?
(直下でないseqは無視　- - - 書かれているseq自体がおかしい為)