Mysql 多级菜单/路径/分类中 id 路径转中文name 路径，Sql实现非递归，循环

需求：

设计数据库时为避免递归多级菜单/部门等常会带上id_path,如

不通过代码查询直接获取name_path,使用 FIND_IN_SET(）+ GROUP_CONCAT:

SELECT a.*, GROUP_CONCAT(b.name ORDER BY b.id_path) AS name_path
FROM table a 
LEFT JOIN table b ON FIND_IN_SET( b.id, a.id_path)
GROUP BY a.id

示例：

已有的库格式无法匹配时需进行转换
表中dept_path是以‘/’分割的，且第一个id带有分割符
截取替换:(REPLACE (SUBSTR(department_path,2),’/’,’,’))得到：

完整sql:
SELECT a.department_id, REPLACE (GROUP_CONCAT(b.department_name ORDER BY b.department_path),',','/') AS name_path FROM t_department a LEFT JOIN t_department b ON FIND_IN_SET(b.department_id,(REPLACE (SUBSTR(a.department_path,2),'/',','))) GROUP BY a.department_id

效果：

补充：

GROUP_CONCAT：分组连接字段，默认分隔符为’,’，内部进行排序使路径由高到低；
结尾可使用having进行筛选+HAVING department_id = #{dept_id}