MySQL的GROUP_CONCAT与分离

Question

假设我有一个表 'pincodes' 作为

------------------------------------- 
| id | name | parent_id | value | 
------------------------------------- 
| 1 | State |  0  | 0 | 
| 2 | City1 |  1  | 20220 | 
| 3 | City2 |  1  | 20221 | 

结果我需要的是

-------------------------- 
| 1 | State  |  | 
| 2 | State>City1 | 20220| 
| 3 | State>City2 | 20221| 
-------------------------- 

什么我尝试了

SELECT id, 
    GROUP_CONCAT(name ORDER BY parent_id SEPARATOR ' > ') AS name 
FROM pincode 
GROUP BY id ORDER BY name; 

而结果为上述查询是

-------------------- 
| 1 | State |  | 
| 2 | City1 | 20220| 
| 3 | City2 | 20221| 
-------------------- 

什么，我需要的是父母的状态应该被追加到每个城市。

注意：任何人都可以提出一个更好的问题标题。

Answer 1

您可以使用下面的查询：

SELECT p1.id, CONCAT(COALESCE(CONCAT(p2.name, '>'), ''), p1.name), p1.value 
FROM pincodes AS p1 
LEFT JOIN pincodes AS p2 ON p1.parent_id = p2.id 
ORDER BY p1.id 

你可以使用LEFT JOIN父记录。使用CONCAT函数可以将父项的名称与子项的名称连接起来。

Demo here

编辑：

上面的查询仅适用于2级的层次结构。对于额外的级别，你必须使用额外的LEFT JOIN操作。对于通用的解决方案，必须使用MySQL中不可用的递归CTE。

这是上面的查询如何可以扩展到处理3层次结构：

SELECT p1.id, 
     CONCAT(COALESCE(CONCAT(p3.name, '>'), ''), 
       COALESCE(CONCAT(p2.name, '>'), ''), 
       p1.name), 
     p1.value 
FROM pincodes AS p1 
LEFT JOIN pincodes AS p2 ON p1.parent_id = p2.id 
LEFT JOIN pincodes AS p3 ON p2.parent_id = p3.id 
ORDER BY p1.id 

Demo here