我有两张table。

Categories
--Id
--Name
--ParentCategoryId (reference to Categories table column Id)

Materials
--Id
--Name
--CategoryId (reference to Categories table column Id)

我想选择指定的材料及其所有父类别

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

Materials table data
--Id Name             CategoryId
  1  mango            3

因此，当我选择ID=1的芒果时，我会在结果集中获得该材料的所有相关类别（食物，水果，异国水果）
我用递归的with子句写了类似的东西

WITH RECURSIVE RecursiveCTE  AS(
    SELECT m."Id", m."Name", c."Id" as "CategoryId", c."Name" "CategoryName"
    FROM "Materials" m
    INNER JOIN "Categories" c on c."Id" = m."CategoryId"
    where m."Id" = 1

    union all

    SELECT rt."Id", rt."Name", c."Id" as "CategoryId", c."Name" "CategoryName"
    FROM RecursiveCTE rt
    INNER JOIN "Categories" c on c."ParentCategoryId" = rt."Id"
)
SELECT * FROM RecursiveCTE