使用sql递归将叶子到树的路径串联成水平列名

Question

我有三张表如下，分别记录类别的树结构、类别信息、类别分类名称：
categorytree：id , parentId , categoryID
category：categoryID，categoryName , level
categorylevel : level , catedorylevelName

外键应该可以看出来吧。
假设表内数据：
categorytree: id parentId categoryID
1 0 1
2 0 2
3 1 3
4 3 4
5 2 7
category: categoryID categoryName level
1 企划 1
2 开发 1
3 年度计划 2
4 项目A 3
7 模块开发 2
categorylevel: level categorylevelName
1 分类一
2 分类二
3 分类三

表结构是为了实现分类的可扩展性和树枝同级的项目名可能重用的考虑，
例如增加到分类四，或年度计划既出现在开发的子项也出现在企划的子项。
父节点ID为零的就是根节点

现在我想根据某个叶子节点，查找出到根节点的路径，然后按分类名作为列值查找出来
例如：查找ID = 4和5 的叶子节点
列：分类一 分类二 分类三
企划 年度计划 项目A
开发 模块开发

列根据categorylevel表确定有几列 叶子节点不一定为第几级别，没有就为空

这样通过sql嵌套查询可以实现吗，我用的数据库是mysql，如何实现呢
求助各位大神啊！！

Answer 1

该回答引用ChatGPT

是的，可以使用 SQL 嵌套查询来实现你的需求。以下是一种可能的解决方案：

SELECT 
  c1.categoryName AS col1, 
  c2.categoryName AS col2, 
  c3.categoryName AS col3
FROM 
  categorytree ct
  JOIN category c1 ON ct.categoryID = c1.categoryID
  LEFT JOIN categorytree ct2 ON ct.parentId = ct2.categoryID
  LEFT JOIN category c2 ON ct2.categoryID = c2.categoryID
  LEFT JOIN categorytree ct3 ON ct2.parentId = ct3.categoryID
  LEFT JOIN category c3 ON ct3.categoryID = c3.categoryID
WHERE 
  ct.id IN (4, 5)
ORDER BY 
  ct.id;

这个查询的逻辑是，首先从 categorytree 表中获取到叶子节点的信息（这里假设叶子节点的 id 是 4 和 5），然后通过 JOIN 操作和 category 表和 categorytree 表自连接的方式，获取到从叶子节点到根节点的所有分类信息。最后根据 categorylevel 表中的信息，将分类名作为列名进行展示。

---

需要注意的是，这个查询假设分类的层级最多有三级，如果需要支持更多层级的分类，需要相应地增加 LEFT JOIN 操作和列名的数量。

---

另外，这个查询的效率可能不是很高，因为使用了多次的表自连接操作。如果数据量很大，可能需要通过其他方式进行优化，比如使用递归查询等。