如何将不同层级的product_groups与specifications合并至最深层级？

需要确定所有有效分类/产品组及其所属所有产品的有效规格，核心规则如下：

• 产品具备不同的规格/特性
• 产品始终挂靠在产品组层级树的最低层级
• 规格可绑定至不同层级，通常仅绑定在最高层级（通用规格）和最低层级（特殊规格如尺寸、压力等）

由于产品关联在最低层级，最优方案是将路径中所有上层级的规格合并至最低层级。

product_groups（产品组表） #

specification_to_group（规格与组关联表） #

result（预期结果表） #

方案一：使用辅助表（通用性较弱） #

首先生成存储叶子节点所有上级路径的临时数据：

SELECT
    lst.id AS lst,
    up1.id AS up1,
    up2.id AS up2,
    up3.id AS up3,
    up4.id AS up4
FROM
    product_groups lst 
        LEFT JOIN
    product_groups up1 ON lst.parent_id = up1.id
        LEFT JOIN
    product_groups up2 ON up1.parent_id = up2.id
        LEFT JOIN
    product_groups up3 ON up2.parent_id = up3.id
        LEFT JOIN
    product_groups up4 ON up3.parent_id = up4.id
WHERE
    lst.id NOT IN
    (SELECT DISTINCT parent_id
        FROM product_groups
        WHERE parent_id IS NOT NULL
)

再通过UNION合并所有层级的规格关联数据：

SELECT g.lst, s.specification_id
FROM ht_tree g
       LEFT JOIN specification_to_group s ON g.lst = s.product_group_id
UNION 
SELECT g.lst, s.specification_id
FROM ht_tree g
       LEFT JOIN specification_to_group s ON g.up1 = s.product_group_id
UNION 
SELECT g.lst, s.specification_id
FROM ht_tree g
       LEFT JOIN specification_to_group s ON g.up2 = s.product_group_id
UNION 
SELECT g.lst, s.specification_id
FROM ht_tree g
       LEFT JOIN specification_to_group s ON g.up3 = s.product_group_id
UNION 
SELECT g.lst, s.specification_id
FROM ht_tree g
       LEFT JOIN specification_to_group s ON g.up4 = s.product_group_id;

方案二：使用CTE（公共表表达式，无需持久化表） #

因无创建表权限且无法保存预处理数据，推荐用递归CTE遍历层级树，直接关联规格数据：

WITH RECURSIVE product_group_hierarchy AS (
    -- 初始化：筛选所有无下级的叶子节点
    SELECT 
        pg.id AS leaf_group_id,
        pg.id AS current_group_id
    FROM product_groups pg
    WHERE pg.id NOT IN (SELECT DISTINCT parent_id FROM product_groups WHERE parent_id IS NOT NULL)
    
    UNION ALL
    
    -- 递归遍历每个叶子节点的所有上级节点
    SELECT 
        pgh.leaf_group_id,
        pg.id AS current_group_id
    FROM product_group_hierarchy pgh
    JOIN product_groups pg ON pgh.current_group_id = pg.parent_id
)
-- 关联规格表并去重，得到最终合并结果
SELECT 
    pgh.leaf_group_id AS product_group_id,
    stg.specification_id
FROM product_group_hierarchy pgh
JOIN specification_to_group stg ON pgh.current_group_id = stg.product_group_id
ORDER BY pgh.leaf_group_id, stg.specification_id;

该方案无需手动指定层级数量，能自动适配任意深度的产品组树，通用性更强。

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