获取部门完整路径数据

1: 获取部门数据 (基础)

SELECT `id`, CONCAT(pid_path,id) as pid_path2,pid_path,title FROM `web_department`;

在这里插入图片描述

2: 获取部门数据 (进阶, 但是是,分隔)

SELECT t1.id, t1.title, CONCAT(t1.pid_path, ',', t1.id) AS pid_path,(SELECT GROUP_CONCAT(t2.title ORDER BY FIND_IN_SET(t2.id, CONCAT(t1.pid_path, ',', t1.id))) FROM web_department AS t2 WHERE FIND_IN_SET(t2.id, CONCAT(t1.pid_path, ',', t1.id))) AS full_path
FROM web_department AS t1;

在这里插入图片描述
3: 获取部门数据 (升华, 可以 / 分隔)

SELECT t1.id, t1.title, CONCAT(t1.pid_path, ',', t1.id) AS pid_path,REPLACE((SELECT GROUP_CONCAT(t2.title ORDER BY FIND_IN_SET(t2.id, CONCAT(t1.pid_path, ',', t1.id)) SEPARATOR '/')FROM web_department AS t2WHERE FIND_IN_SET(t2.id, CONCAT(t1.pid_path, ',', t1.id))), ',', '/') AS full_path
FROM web_department AS t1;

在这里插入图片描述

4: 获取部门数据 (再次升华, 可以条件搜索)

SELECT *
FROM (SELECT t1.id, t1.title, CONCAT(t1.pid_path, ',', t1.id) AS pid_path,REPLACE((SELECT GROUP_CONCAT(t2.title ORDER BY FIND_IN_SET(t2.id, CONCAT(t1.pid_path, ',', t1.id)) SEPARATOR '/')FROM web_department AS t2WHERE FIND_IN_SET(t2.id, CONCAT(t1.pid_path, ',', t1.id))), ',', '/') AS full_pathFROM web_department AS t1
) AS temp
WHERE temp.full_path = '总经办/研发部/测试部';