トップ 差分 一覧 ソース 検索 ヘルプ PDF RSS ログイン

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)