如何使父母从子女身份证中最多5级层次结构

Question

我创建了一个带有名称" content_folder"和插入值的表。

    cf_id   cf_parent_id    cf_name 
 --------------------------------------
    1           0           root    
    2           1           US Blenders 
    3           2           US Blenders Chil11  
    4           1           Australian Blenderss    
    5           1           US Blenders Chil11 -2   
    40003       1           Child       
    40206       1           Child 111   
    40211       2           New 

我想显示5个级别的5个级别的文件夹层次结构，在droplown中被'>'，所以我希望以以下方式进行结果

   cf_id    path
--------------------------------------
     1          root
     2          US Blenders
     3          US Blenders > US Blenders Chil11
     4          Australian Blenderss
     5          US Blenders Chil11 -2
    40003       Child
    40206       Child 111
    40211       US Blenders > New

我通过编写查询来尝试，但输出结果不合适。

SELECT t.cf_id, 
       Group_concat(anc.cf_name ORDER BY anc.cf_name SEPARATOR ' > ') AS path 
FROM   content_folder AS t 
       JOIN content_folder AS anc 
         ON t.cf_name LIKE Concat(anc.cf_name, '%') 
GROUP  BY t.cf_id; 

您能建议我更好的解决方案吗？我正在使用MySQL 5.7，因此我认为此版本不支持CTE。

Answer 1

我对我的数据库做了这样的事情。这是我的做法，我更改了一些事情，并在最后添加了两个更新语句以获取所需的输出，但是我认为您将获得所发生的要点：

DECLARE @temp TABLE
(
    id INT,
    parent_id INT,
    name NVARCHAR(100),
    path NVARCHAR(MAX)
)
INSERT INTO @temp (id,parent_id,name) VALUES
(1,0,'root'),
(2,1,'US Blenders'),
(3,2,'US Blenders Chil11'),
(4,1,'Australian Blenderss'),
(5,1,'US Blenders Chil11 -2'),
(40003,1,'Child'),
(40206,1,'Child 111'),
(40211,2,'New')
DECLARE @counter TABLE ( id INT )
DECLARE @current_id INT;
DECLARE @loc_id INT;
INSERT INTO @counter SELECT id FROM @temp
SELECT * FROM @temp
WHILE (SELECT COUNT(*) FROM @counter) > 0
BEGIN
    SET @current_id = (SELECT TOP 1 id FROM @counter)
    SET @loc_id = (SELECT parent_id FROM @temp WHERE id = @current_id)
    WHILE @loc_id IS NOT NULL AND @loc_id != 0
    BEGIN
        UPDATE @temp
        SET path = ' > ' + (SELECT name FROM @temp WHERE id = @loc_id) + CASE WHEN path IS NULL THEN '' ELSE path END
        WHERE id = @current_id
        SET @loc_id = (SELECT parent_id FROM @temp WHERE id = @loc_id)
    END
    DELETE FROM @counter 
    WHERE id = @current_id
END
UPDATE @temp
SET path = REPLACE(REPLACE(path,' > root',''),' > ','')
UPDATE @temp
SET path = CASE WHEN LEN(path) > 0 THEN path + ' > ' + name ELSE name END
SELECT * FROM @temp

输出：

id      parent_id   name                    path
1       0           root                    root
2       1           US Blenders             US Blenders
3       2           US Blenders Chil11      US Blenders > US Blenders Chil11
4       1           Australian Blenderss    Australian Blenderss
5       1           US Blenders Chil11 -2   US Blenders Chil11 -2
40003   1           Child                   Child
40206   1           Child 111               Child 111
40211   2           New                     US Blenders > New