如何基于表内多级层级结构显示商品的父类别？

首先明确需求：你需要的是商品所属分组的直接父类别，还是最顶层的根父类别？以下分两种情况给出解决方案：

原始查询与类别表信息 #

你的基础商品查询语句：

SELECT 
    Item.StoreID
    ,Item.ItemID
    ,Item.Name
    ,Item.PricingID
    ,Pricing.Name 
    ,Item.ItemGroupID
FROM 
    Item
JOIN 
    Pricing ON Pricing.PricingID = Item.PricingID
WHERE 
    Item.StoreID = 11

类别表（假设表名为ItemGroup）的结构及数据：

-- 表结构
CREATE TABLE ItemGroup (
    ItemGroupID INT,
    ParentGroupID INT,
    GroupName VARCHAR(50)
);

-- 示例数据
INSERT INTO ItemGroup VALUES
(1, null, '顶层类别1'),
(2, null, '顶层类别2'),
(3, 1, '子类别3'),
(4, 3, '子类别4'),
(5, null, '顶层类别5');

情况1：显示直接父类别 #

如果只需要商品所属分组的直接上级类别，通过两次左联ItemGroup表即可实现：

SELECT 
    Item.StoreID,
    Item.ItemID,
    Item.Name AS ItemName,
    Item.PricingID,
    Pricing.Name AS PricingName,
    Item.ItemGroupID,
    parentIG.ItemGroupID AS DirectParentGroupID,
    parentIG.GroupName AS DirectParentGroupName
FROM 
    Item
JOIN 
    Pricing ON Pricing.PricingID = Item.PricingID
-- 关联商品所属的当前分组
LEFT JOIN 
    ItemGroup currentIG ON Item.ItemGroupID = currentIG.ItemGroupID
-- 关联当前分组的直接父分组
LEFT JOIN 
    ItemGroup parentIG ON currentIG.ParentGroupID = parentIG.ItemGroupID
WHERE 
    Item.StoreID = 11;

情况2：显示顶层根父类别 #

如果需要找到层级链中最上层的父类别（比如分组4的顶层父是分组1），需要用递归CTE遍历整个层级关系，获取每个分组的根节点：

WITH RecursiveItemGroup AS (
    -- 锚点：先筛选所有顶层父类别（ParentGroupID为null的记录）
    SELECT 
        ItemGroupID,
        ParentGroupID,
        GroupName,
        ItemGroupID AS RootGroupID,
        GroupName AS RootGroupName
    FROM ItemGroup
    WHERE ParentGroupID IS NULL

    UNION ALL

    -- 递归：子类别关联父类别，继承根节点信息
    SELECT 
        ig.ItemGroupID,
        ig.ParentGroupID,
        ig.GroupName,
        rig.RootGroupID,
        rig.RootGroupName
    FROM ItemGroup ig
    JOIN RecursiveItemGroup rig ON ig.ParentGroupID = rig.ItemGroupID
)
SELECT 
    Item.StoreID,
    Item.ItemID,
    Item.Name AS ItemName,
    Item.PricingID,
    Pricing.Name AS PricingName,
    Item.ItemGroupID,
    rig.RootGroupID AS TopParentGroupID,
    rig.RootGroupName AS TopParentGroupName
FROM 
    Item
JOIN 
    Pricing ON Pricing.PricingID = Item.PricingID
-- 关联递归CTE，获取当前分组的顶层父类别
JOIN 
    RecursiveItemGroup rig ON Item.ItemGroupID = rig.ItemGroupID
WHERE 
    Item.StoreID = 11;

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