我有两张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
1条答案
按热度按时间nhaq1z211#
已解决))