分层表设计中如何查询分类树指定节点全父级及代次层级数据

不需要自定义SQL函数 #

主流现代关系型数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）都内置了递归公用表表达式（递归CTE），完全适配这种分层树结构的向上回溯查询需求，不需要额外编写自定义SQL函数。
假设你存储分类树的表名为category_tree（请替换为你实际的表名），可以直接用以下查询实现需求：

基础需求：返回ID=5节点的完整父级路径 #

这个查询的输出和你示例的期望结果完全一致，包含从根节点到目标节点的所有层级：

WITH RECURSIVE node_path AS (
    -- 锚点：从指定目标节点开始（这里是ID=5的Series X）
    SELECT ID, Name, Parent
    FROM category_tree
    WHERE ID = 5
    UNION ALL
    -- 递归体：循环关联当前节点的父节点，直到找不到父节点为止
    SELECT t.ID, t.Name, t.Parent
    FROM category_tree t
    INNER JOIN node_path n ON t.ID = n.Parent
)
-- 按ID排序后即为从根到目标节点的顺序
SELECT * FROM node_path
ORDER BY ID;

输出结果：

进阶需求：附带各节点的代次层级 #

只要在递归逻辑里增加代次计数字段，就能得到你需要的Generation字段：

WITH RECURSIVE node_path AS (
    -- 从目标节点开始，初始代次设为0（因为是从下往上回溯）
    SELECT ID, Name, Parent, 0 AS generation
    FROM category_tree
    WHERE ID = 5
    UNION ALL
    -- 每往上查一层父节点，代次加1
    SELECT t.ID, t.Name, t.Parent, n.generation + 1
    FROM category_tree t
    INNER JOIN node_path n ON t.ID = n.Parent
)
-- 代次倒序后，根节点代次为0，符合你的预期
SELECT ID, Name, Parent, generation
FROM node_path
ORDER BY generation DESC;

输出结果：

小提示 #

• 如果不需要包含目标节点本身，只要在最终查询里加过滤条件WHERE ID != 5，或者直接把锚点的初始查询改成WHERE ID = (SELECT Parent FROM category_tree WHERE ID = 5)，直接从目标节点的父节点开始回溯。
• 递归会自动终止：因为根节点的Parent值为0，不存在ID=0的分类行，所以递归到根节点后就会自动停止，不需要额外写终止判断逻辑。
• 仅当你使用MySQL 5.x及更早的不支持递归CTE的数据库版本时，才需要考虑编写自定义函数或者用变量拼接层级，目前主流环境都已经升级到MySQL 8.0+，完全不需要额外写自定义函数。

内容的提问来源于stack exchange，提问作者John Sly