待ってました。mysqlで再帰SQLが使えるようになった。ORACLEではかなり前からあった。羨ましかった。早速使ってみた。これでSQLだけで部品展開ができる。 ![部品展開|http://www21051ue.sakura.ne.jp/mmt/frame.cgi] mysql> with recursive product as ( select 親部品コード ,子部品コード ,員数 ,1 level ,員数 as 必要数 ,concat('/',親部品コード,'/',子部品コード) as path from 構成表 where 親部品コード = 'A1' union all select 構成表.親部品コード ,構成表.子部品コード ,構成表.員数 ,product.level + 1 as level ,product.必要数 * 構成表.員数 ,concat(product.path,'/',構成表.子部品コード) as path from 構成表 ,product where 構成表.親部品コード = product.子部品コード ) select * from product order by path; +--------------------+--------------------+--------+-------+-----------+--------------------------------------------+ | 親部品コード | 子部品コード | 員数 | level | 必要数 | path | +--------------------+--------------------+--------+-------+-----------+--------------------------------------------+ | A1 | B1 | 2 | 1 | 2 | /A1/B1 | | B1 | B2 | 2 | 2 | 4 | /A1/B1/B2 | | B1 | C1 | 10 | 2 | 20 | /A1/B1/C1 | | B1 | C2 | 2 | 2 | 4 | /A1/B1/C2 | | A1 | frame_tree | 1 | 1 | 1 | /A1/frame_tree | | frame_tree | CSS | 2 | 2 | 2 | /A1/frame_tree/CSS | | frame_tree | frame.cgi | 2 | 2 | 2 | /A1/frame_tree/frame.cgi | | frame_tree | js | 2 | 2 | 2 | /A1/frame_tree/js | | frame_tree | tree02.cgi | 3 | 2 | 3 | /A1/frame_tree/tree02.cgi | | tree02.cgi | ajax.pm | 2 | 3 | 6 | /A1/frame_tree/tree02.cgi/ajax.pm | | ajax.pm | dom.js | 1 | 4 | 6 | /A1/frame_tree/tree02.cgi/ajax.pm/dom.js | | ajax.pm | screen.css | 1 | 4 | 6 | /A1/frame_tree/tree02.cgi/ajax.pm/screen.c | | ajax.pm | tree.css | 1 | 4 | 6 | /A1/frame_tree/tree02.cgi/ajax.pm/tree.css | | ajax.pm | treeview.js | 1 | 4 | 6 | /A1/frame_tree/tree02.cgi/ajax.pm/treeview | | ajax.pm | yahoo.js | 1 | 4 | 6 | /A1/frame_tree/tree02.cgi/ajax.pm/yahoo.js | | tree02.cgi | mmt.pm | 2 | 3 | 6 | /A1/frame_tree/tree02.cgi/mmt.pm | +--------------------+--------------------+--------+-------+-----------+--------------------------------------------+ 16 rows in set (0.01 sec) mysql> mysql> show create table 構成表 \G *************************** 1. row *************************** Table: 構成表 Create Table: CREATE TABLE `構成表` ( `親部品コード` varchar(20) NOT NULL DEFAULT '', `子部品コード` varchar(20) NOT NULL DEFAULT '', `員数` decimal(10,0) NOT NULL, `乗数` decimal(10,0) NOT NULL, `UPD_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`親部品コード`,`子部品コード`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> !エラーがでる ERROR 1406 (22001): Data too long for column 'path' at row 2 *sql modeを変更しないとエラーがでるのでsql modeを設定後に再実行 mysql> set session sql_mode = ''; *ステートメントが切り捨てやエラーを生成しないように、この問題に対処するには、非再帰的なSELECTでCAST（）を使用して、列を広くします。(https://dev.mysql.com/doc/refman/8.0/en/with.html より) mysql> with recursive product as ( select 親部品コード ,子部品コード ,員数 ,1 level ,員数 as 必要数 ,cast(concat('/',親部品コード,'/',子部品コード) as char(100)) as path from 構成表 where 親部品コード = 'A1' union all select 構成表.親部品コード ,構成表.子部品コード ,構成表.員数 ,product.level + 1 as level ,product.必要数 * 構成表.員数 ,concat(product.path,'/',構成表.子部品コード) as path from 構成表 ,product where 構成表.親部品コード = product.子部品コード ) select * from product order by path; +--------------------+--------------------+--------+-------+-----------+-----------------------------------------------+ | 親部品コード | 子部品コード | 員数 | level | 必要数 | path | +--------------------+--------------------+--------+-------+-----------+-----------------------------------------------+ | A1 | B1 | 1 | 1 | 1 | /A1/B1 | | B1 | B2 | 0 | 2 | 0 | /A1/B1/B2 | | B1 | C1 | 1 | 2 | 1 | /A1/B1/C1 | | B1 | C2 | 1 | 2 | 1 | /A1/B1/C2 | | A1 | frame_tree | 0 | 1 | 0 | /A1/frame_tree | | frame_tree | CSS | 0 | 2 | 0 | /A1/frame_tree/CSS | | frame_tree | frame.cgi | 0 | 2 | 0 | /A1/frame_tree/frame.cgi | | frame_tree | js | 0 | 2 | 0 | /A1/frame_tree/js | | frame_tree | tree02.cgi | 0 | 2 | 0 | /A1/frame_tree/tree02.cgi | | tree02.cgi | ajax.pm | 0 | 3 | 0 | /A1/frame_tree/tree02.cgi/ajax.pm | | ajax.pm | dom.js | 0 | 4 | 0 | /A1/frame_tree/tree02.cgi/ajax.pm/dom.js | | ajax.pm | screen.css | 0 | 4 | 0 | /A1/frame_tree/tree02.cgi/ajax.pm/screen.css | | ajax.pm | tree.css | 0 | 4 | 0 | /A1/frame_tree/tree02.cgi/ajax.pm/tree.css | | ajax.pm | treeview.js | 0 | 4 | 0 | /A1/frame_tree/tree02.cgi/ajax.pm/treeview.js | | ajax.pm | yahoo.js | 0 | 4 | 0 | /A1/frame_tree/tree02.cgi/ajax.pm/yahoo.js | | tree02.cgi | mmt.pm | 0 | 3 | 0 | /A1/frame_tree/tree02.cgi/mmt.pm | +--------------------+--------------------+--------+-------+-----------+-----------------------------------------------+ 16 rows in set (0.00 sec) !!カンマ区切りの文字列を行に変換 !再帰SQL 1つのカラムにカンマ区切りで入っている複数の単語を分割し行に（縦展開）展開する。（※設計時に正規化しとけって話ですが・・） select 名前,好きなもの from 好き嫌い; +-----------------+-----------------------------------------+ | 名前 | 好きなもの | +-----------------+-----------------------------------------+ | 山田　太郎 | りんご,バナナ,みかん,いちご | | 斎藤　花子 | 苺,無花果 | +-----------------+-----------------------------------------+ 再帰共通テーブル式を使いカンマ区切りの文字列を分解 *再帰共通テーブル式はwith recursiveから始まる。 *SUBSTRINGでカンマの前と後で最初の単語と残りの文字列に分解する。（カンマが無いと分解できないので文字列の最後にカンマを追加） *UNION ALLの次のSELECT文で残りの文字列に文字がある間共通テーブルを参照し残りの文字列をカンマの前と後ろで分解する。 with recursive work as ( select 名前 ,SUBSTRING(concat(好きなもの,","),1,LOCATE(',',concat(好きなもの,","))-1) AS 最初の単語 ,SUBSTRING(concat(好きなもの,","),LOCATE(',',concat(好きなもの,","))+1,LENGTH(concat(好きなもの,","))) AS 残りの文字列 from 好き嫌い union all select 名前 ,SUBSTRING(残りの文字列,1,LOCATE(',',残りの文字列)-1) AS 最初の単語 ,SUBSTRING(残りの文字列,LOCATE(',',残りの文字列)+1,LENGTH(残りの文字列)) AS 残りの文字列 from work where 残りの文字列 <> '' ) select 名前,最初の単語 as 好物 from work ; +-----------------+-----------+ | 名前 | 好物 | +-----------------+-----------+ | 山田　太郎 | りんご | | 斎藤　花子 | 苺 | | 山田　太郎 | バナナ | | 斎藤　花子 | 無花果 | | 山田　太郎 | みかん | | 山田　太郎 | いちご | +-----------------+-----------+ !テーブル参照 カンマで区切られた単語でテーブルを参照できる場合はテーブルを参照し縦展開する。 select 名前,分類名 from 好き嫌い left join 分類名称 on 好きなもの like concat('%',分類名,'%') ; +-----------------+-----------+ | 名前 | 分類名 | +-----------------+-----------+ | 山田　太郎 | りんご | | 山田　太郎 | バナナ | | 山田　太郎 | みかん | | 斎藤　花子 | 苺 | | 斎藤　花子 | 無花果 | +-----------------+-----------+