Mariadb 10.5递归关联分类表与场所表获取全层级分类

针对你的需求，直接使用MariaDB 10.5支持的**递归CTE（公共表表达式）**即可完成关联，无需临时表。以下是完整实现：

假设表结构 #

先明确两张表的基础结构（如果你的表字段不同，替换对应字段即可）：

• Premise表：premise_id（主键）、category_id（关联分类表的外键）、premise_name（场所名称）
• Category表：category_id（主键）、parent_id（父分类ID，顶级分类可设为NULL或0）、category_name（分类名称）

完整SQL代码 #

WITH RECURSIVE category_hierarchy AS (
    -- 基础节点：所有分类，标记其原始所属的分类ID（root_category_id）
    SELECT
        category_id,
        parent_id,
        category_name,
        category_id AS root_category_id,
        1 AS depth -- 深度：1为当前分类，数值越大代表层级越高（越靠近父级）
    FROM Category
    UNION ALL
    -- 递归遍历：向上获取父分类，保留原始分类ID
    SELECT
        c.category_id,
        c.parent_id,
        c.category_name,
        ch.root_category_id,
        ch.depth + 1 AS depth
    FROM Category c
    INNER JOIN category_hierarchy ch 
        ON c.category_id = ch.parent_id
)
-- 关联场所表，获取每个场所的全部分类层级
SELECT
    p.premise_id,
    p.premise_name,
    ch.category_id,
    ch.category_name,
    ch.parent_id,
    ch.depth,
    ch.root_category_id AS original_category_id -- 场所最初关联的分类ID
FROM Premise p
INNER JOIN category_hierarchy ch 
    ON p.category_id = ch.root_category_id
-- 按需排序：先按场所ID，再按深度（原始分类在前，父分类在后）
ORDER BY p.premise_id, ch.depth;

关键说明 #

1. 递归逻辑：通过root_category_id字段保留每个分类节点对应的原始子分类ID，这样关联Premise表时，只需匹配p.category_id = ch.root_category_id，就能把该场所所属分类及其所有父分类一次性拉取出来。
2. 兼容任意ID顺序：递归基于parent_id关联，完全不依赖子分类ID与父分类ID的大小关系，符合你的需求。
3. 终止条件：当父分类的parent_id为NULL时，递归自动终止，不会出现无限循环。

如果你已经有自己的分类递归查询语句，只需在递归CTE中新增root_category_id字段（保留原始分类ID），再按上述方式与Premise表关联即可。

内容的提问来源于stack exchange，提问作者Jan Janáček