mysql数组递归查询

• 假设有下面一张表，主键id对应的每行数据中，parent表示该行的父类id，path表示该行的层级关系，grade表示层级深度。

mysql> select * from zt_module;
+------+------------------------+--------+-----------------------+-------+
| id   | name                   | parent | path                  | grade |
+------+------------------------+--------+-----------------------+-------+
| 2906 | 运营端-系统设置        |      0 | ,2906,                |     1 |
| 2990 | 客户管理               |   2906 | ,2906,2990,           |     2 |
| 3080 | 客户会费管理           |   2990 | ,2906,2990,3080,      |     3 |
| 1124 | 运营端                 |      0 | ,1124,                |     1 |
| 1130 | 汽车经销产业链         |   1124 | ,1124,1130,           |     2 |
| 1137 | 融资管理               |   1130 | ,1124,1130,1137,      |     3 |
| 1217 | 在库管理页面           |   1137 | ,1124,1130,1137,1217, |     4 |
+------+------------------------+--------+-----------------------+-------+

• 需求：若要得到每行的完整层级，也就是从子类id查询所有父类id对应的name（不限层级），如下所示：

+------+-------+------------------------+-----------------------------------------------------------------+
| id   | grade | name                   | xxx                                                             |
+------+-------+------------------------+-----------------------------------------------------------------+
| 1124 |     1 | 运营端                 | 运营端                                                          |
| 1130 |     2 | 汽车经销产业链         | 运营端\汽车经销产业链                                           |
| 1137 |     3 | 融资管理               | 运营端\汽车经销产业链\融资管理                                  |
| 1217 |     4 | 在库管理页面           | 运营端\汽车经销产业链\融资管理\在库管理页面                     |
| 2906 |     1 | 运营端-系统设置        | 运营端-系统设置                                                 |
| 2990 |     2 | 客户管理               | 运营端-系统设置\客户管理                                        |
| 3080 |     3 | 客户会费管理           | 运营端-系统设置\客户管理\客户会费管理                           |
+------+-------+------------------------+-----------------------------------------------------------------+

• 附：准备测试数据

CREATE DATABASE IF NOT EXISTS bak;
USE bak;
DROP TABLE IF EXISTS zt_module;
CREATE TABLE zt_module (id int, name char(60), parent int, path char(255), grade int);

INSERT INTO zt_module VALUES
 (2906,'运营端-系统设置',0,',2906,',1)
,(2990,'客户管理',2906,',2906,2990,',2)
,(3080,'客户会费管理',2990,',2906,2990,3080,',3)
,(1124,'运营端',0,',1124,',1)
,(1130,'汽车经销产业链',1124,',1124,1130,',2)
,(1137,'融资管理',1130,',1124,1130,1137,',3)
,(1217,'在库管理页面',1137,',1124,1130,1137,1217,',4);

• 解决方案：参考如下

select tmp.id, tmp.grade, tmp.name 
  ,group_concat(z2.name order by tmp.help_topic_id SEPARATOR '\\') as 'xxx' 
from (
  select z1.id,z1.name,z1.grade,ht.help_topic_id
    ,substring_index(substring_index(z1.path, ',', ht.help_topic_id+1), ',', -1) as sub_id
  from zt_module z1
  inner join mysql.help_topic ht
  on ht.help_topic_id < (length(z1.path) - length(replace(z1.path, ',', '')) + 1)
  ) as tmp 
inner join zt_module as z2 on tmp.sub_id = z2.id 
group by tmp.id;