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)
カンマ区切りの文字列を行に変換
with recursive product as (
select vchReportID,vchItemID,intOrder,vchNextParts,vchNextParts as vchNextParts2
,SUBSTRING(concat(vchNextParts,","),1,LOCATE(',',concat(vchNextParts,","))-1) AS col2
,SUBSTRING(concat(vchNextParts,","),LOCATE(',',concat(vchNextParts,","))+1,LENGTH(concat(vchNextParts,","))) AS col3
from survey_report_info
union all
select vchReportID,vchItemID,intOrder,col3,vchNextParts2
,SUBSTRING(col3,1,LOCATE(',',col3)-1) AS col2
,SUBSTRING(col3,LOCATE(',',col3)+1,LENGTH(col3)) AS col3
from product a
where a.col3 <> ''
) select vchReportID,vchItemID,col2,vchNextParts2,col3 from product
order by vchReportID,intOrder
;
select m.vchReportID,m.vchItemID,a.vchItemID,m.intOrder,a.intOrder,m.vchNextParts from survey_report_info m
left join survey_report_info a on
a.vchReportID = m.vchReportID and m.vchNextParts like concat('%',a.vchItemID,'%')
order by m.vchReportID,m.intOrder
;
- 無精・短気・傲慢