PostgreSQL查询指定物料及其所有父级分类方案

表结构与测试数据 #

表定义 #

• Categories表
  • Id
  • Name
  • ParentCategoryId（自关联到Categories表的Id字段）
• Materials表
  • Id
  • Name
  • CategoryId（关联到Categories表的Id字段）

测试数据 #

Categories表数据

Id  Name                 ParentCategoryId
------------------------------------------
  1  food                 null
  2  fruits               1
  3  exotic fruits        2
  4  IT equipment         null

Materials表数据

Id Name             CategoryId
 -------------------------------
  1  mango            3

需求 #

查询Id=1的物料（芒果）及其所有关联的父级分类，期望结果包含food、fruits、exotic fruits三个分类。

原SQL的问题 #

你编写的递归CTE存在两个关键错误：

1. 初始查询未包含分类的ParentCategoryId字段，导致递归时无法追踪父级分类；
2. 递归部分的关联条件逻辑颠倒：c."ParentCategoryId" = rt."Id"用物料Id关联分类，而非当前分类的父ID去匹配上层分类的Id。

修正后的SQL语句 #

WITH RECURSIVE RecursiveCTE AS
(
    -- 初始步骤：获取指定物料的直接关联分类，同时保留该分类的父ID用于递归
    SELECT 
        m."Id" AS "MaterialId", 
        m."Name" AS "MaterialName", 
        c."Id" AS "CategoryId", 
        c."Name" AS "CategoryName",
        c."ParentCategoryId"
    FROM "Materials" m
    INNER JOIN "Categories" c ON c."Id" = m."CategoryId"
    WHERE m."Id" = 1

    UNION ALL

    -- 递归步骤：根据当前分类的父ID，向上查找父级分类
    SELECT 
        rt."MaterialId", 
        rt."MaterialName", 
        c."Id" AS "CategoryId", 
        c."Name" AS "CategoryName",
        c."ParentCategoryId"
    FROM RecursiveCTE rt
    INNER JOIN "Categories" c ON c."Id" = rt."ParentCategoryId"
)
-- 最终只返回需要的字段，排除用于递归的ParentCategoryId
SELECT "MaterialId", "MaterialName", "CategoryId", "CategoryName" 
FROM RecursiveCTE;

执行结果 #

运行上述SQL后，将得到符合需求的结果集：

MaterialId | MaterialName | CategoryId | CategoryName
-----------|--------------|------------|--------------
1          | mango        | 3          | exotic fruits
1          | mango        | 2          | fruits
1          | mango        | 1          | food

内容的提问来源于stack exchange，提问作者Emil Abbas