MySQL 8
待ってました。mysqlで再帰SQLが使えるようになった。ORACLEではかなり前からあった。羨ましかった。早速使ってみた。これでSQLだけで部品展開ができる。
部品展開
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('%',分類名,'%')
;
+-----------------+-----------+ | 名前 | 分類名 | +-----------------+-----------+ | 山田 太郎 | りんご | | 山田 太郎 | バナナ | | 山田 太郎 | みかん | | 斎藤 花子 | 苺 | | 斎藤 花子 | 無花果 | +-----------------+-----------+
- 無精・短気・傲慢