如何在关联查询中使用带参数的递归CTE查询商品全量父分类

错误原因 #

你遇到的报错是SQL标准的派生表作用域限制：FROM子句中的普通派生表无法引用同一FROM列表中位于它之前的表的字段，也就是你写的子查询无法访问外层Product表的p.CategoryId。而用户变量的方案受SQL执行顺序影响，结果不稳定，不推荐使用。

方案1：使用LATERAL JOIN（标准SQL写法，兼容性好） #

LATERAL是SQL:2011标准引入的特性，允许派生表引用前面FROM列表中的表字段，MariaDB 10.3+、PostgreSQL 9.3+均原生支持，直接修改你的原查询即可：

SELECT p.*, C.*
FROM Product p
CROSS JOIN LATERAL (
    WITH RECURSIVE getCategories as (
        SELECT id, name, ParentCategoryId as parent_id, updatedAt
        FROM Category
        WHERE id = p.CategoryId
        UNION ALL
        SELECT category.id, category.name, category.ParentCategoryId as parent_id, category.updatedAt
        FROM Category category
        JOIN getCategories parentCategory ON category.id = parentCategory.parent_id
    )
    SELECT * FROM getCategories
) C

方案2：预生成全量分类-祖先映射（兼容不支持LATERAL的低版本数据库） #

如果你的数据库版本不支持LATERAL，可以先通过递归CTE生成所有分类和它所有上级分类的映射关系，再和商品表关联，只要数据库支持递归CTE即可运行：

WITH RECURSIVE category_ancestors AS (
    -- 锚点成员：每个分类自身作为叶子节点
    SELECT 
        id AS leaf_category_id,
        id,
        name,
        ParentCategoryId AS parent_id,
        updatedAt
    FROM Category
    UNION ALL
    -- 递归成员：向上关联父分类，保留原始叶子分类ID
    SELECT 
        ca.leaf_category_id,
        c.id,
        c.name,
        c.ParentCategoryId AS parent_id,
        c.updatedAt
    FROM Category c
    JOIN category_ancestors ca ON c.id = ca.parent_id
)
SELECT p.*, ca.*
FROM Product p
JOIN category_ancestors ca ON p.CategoryId = ca.leaf_category_id

该方案在分类数据量不大的场景下，性能比逐行递归的LATERAL方案更好，因为只需要对分类表做一次递归计算。

可选：合并分类为完整路径字符串 #

如果你需要将多级分类合并为类似 一级分类 > 二级分类 > 三级分类 的完整路径，可以对结果做聚合：

MariaDB版本

SELECT 
    p.*,
    GROUP_CONCAT(ca.name ORDER BY ca.parent_id IS NULL DESC SEPARATOR ' > ') AS full_category_path
FROM Product p
JOIN category_ancestors ca ON p.CategoryId = ca.leaf_category_id
GROUP BY p.id;

PostgreSQL版本

SELECT 
    p.*,
    STRING_AGG(ca.name, ' > ' ORDER BY ca.parent_id IS NULL DESC) AS full_category_path
FROM Product p
JOIN category_ancestors ca ON p.CategoryId = ca.leaf_category_id
GROUP BY p.id;

小提示 #

你原递归逻辑中上级分类的updatedAt字段错误引用了子节点的updatedAt，上述代码已经修正为取父分类自身的updatedAt，如果不需要去重可以将UNION替换为UNION ALL提升性能。

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