[MySQL] WITH RECURSIVE (CTE) 結果を INSERT INTO する方法

inno
2023-10-23 20:11 248 0
[MySQL] WITH RECURSIVE (CTE) 結果を INSERT INTO する方法


DROP TEMPORARY TABLE IF EXISTS tempFinalData;

CREATE TEMPORARY TABLE tempFinalData
(
Sn int,
    ParentID int,
    DataValue varchar(10),
    Depth tinyint,
    Navi varchar(50)
);



DROP TABLE IF EXISTS tempData;

CREATE TABLE tempData
(
Sn int,
    ParentID int,
    DataValue varchar(10)
);


INSERT INTO tempData (Sn, ParentID, DataValue) values (1, 0, 'A_0');
INSERT INTO tempData (Sn, ParentID, DataValue) values (2, 0, 'B_0');
INSERT INTO tempData (Sn, ParentID, DataValue) values (3, 1, 'A_1');
INSERT INTO tempData (Sn, ParentID, DataValue) values (4, 2, 'B_1');
INSERT INTO tempData (Sn, ParentID, DataValue) values (5, 1, 'A_2');
INSERT INTO tempData (Sn, ParentID, DataValue) values (6, 2, 'B_2');
INSERT INTO tempData (Sn, ParentID, DataValue) values (7, 3, 'A_3');
INSERT INTO tempData (Sn, ParentID, DataValue) values (8, 3, 'A_4');
INSERT INTO tempData (Sn, ParentID, DataValue) values (9, 7, 'A_5');
INSERT INTO tempData (Sn, ParentID, DataValue) values (10, 7, 'A_6');


INSERT INTO tempFinalData (Sn, ParentID, DataValue, Depth, Navi)
(
WITH RECURSIVE tempCteData (Sn, ParentID, DataValue, Depth, Navi)
AS
(
SELECT Sn, ParentID, DataValue, 0 AS Depth, cast(DataValue as char(500) charset utf8mb4) AS Navi
        FROM tempData
        WHERE ParentID = 0
        UNION ALL
        SELECT A.Sn, A.ParentID, A.DataValue, B.Depth+1 AS Depth, concat(B.DataValue, ' 〉 ', A.DataValue) AS Navi
        FROM tempData AS A JOIN tempCteData AS B
        ON A.ParentID = B.Sn AND A.ParentID > 0 

)
    
    SELECT Sn, ParentID, DataValue, Depth, Navi from tempCteData
);

SELECT * FROM tempFinalData;

コメント