关联两个层级自引用表的技术咨询：属性树与用户值表

嗨，我来帮你理清楚怎么关联这两张自引用表来实现你的需求。首先咱们得先明确两张表的典型结构，这样更容易理解后续的关联逻辑：

1. 先定义两张核心表的典型结构 #

首先是属性层级树表（咱们就叫它attribute_hierarchy吧），用来存储多根的属性层级：

CREATE TABLE attribute_hierarchy (
    attribute_id INT PRIMARY KEY,
    attribute_name VARCHAR(100) NOT NULL,
    parent_attribute_id INT NULL, -- 自引用父节点，NULL代表根节点
    -- 你还可以加其他字段，比如属性类型、描述等
    FOREIGN KEY (parent_attribute_id) REFERENCES attribute_hierarchy(attribute_id)
);

然后是用户属性值分配表（命名为user_attribute_values），自引用结构用来支持多组取值的关联：

CREATE TABLE user_attribute_values (
    value_id INT PRIMARY KEY,
    user_id INT NOT NULL, -- 绑定指定用户
    attribute_id INT NOT NULL, -- 关联属性层级树的属性
    value VARCHAR(200) NOT NULL, -- 属性的具体取值
    parent_value_id INT NULL, -- 自引用父取值，用来标记同一组内的祖先属性取值
    -- 可选字段：取值生效时间、状态等
    FOREIGN KEY (attribute_id) REFERENCES attribute_hierarchy(attribute_id),
    FOREIGN KEY (parent_value_id) REFERENCES user_attribute_values(value_id)
);

2. 核心关联逻辑：递归CTE + 层级匹配 #

因为两张表都是自引用的层级结构，咱们需要分别递归遍历属性的完整祖先链和用户取值的完整祖先链，再通过层级和属性ID的对应关系把它们关联起来。

步骤1：获取每个属性的完整祖先路径（包含自身）

用递归CTE遍历属性树，得到每个属性从根到自身的完整路径：

WITH recursive_attribute_hierarchy AS (
    -- 锚点：先抓所有根节点（父节点为NULL）
    SELECT 
        attribute_id,
        attribute_name,
        parent_attribute_id,
        ARRAY[attribute_id] AS attribute_path, -- 记录属性链路径，方便后续匹配
        1 AS level
    FROM attribute_hierarchy
    WHERE parent_attribute_id IS NULL

    UNION ALL

    -- 递归：子节点关联父节点，拼接路径
    SELECT 
        ah.attribute_id,
        ah.attribute_name,
        ah.parent_attribute_id,
        rah.attribute_path || ah.attribute_id AS attribute_path,
        rah.level + 1 AS level
    FROM attribute_hierarchy ah
    JOIN recursive_attribute_hierarchy rah ON ah.parent_attribute_id = rah.attribute_id
)
SELECT * FROM recursive_attribute_hierarchy;

步骤2：获取用户的每组取值及其完整祖先取值链

同样用递归CTE遍历用户取值表，标记每个取值所属的取值组链：

WITH recursive_user_values AS (
    -- 锚点：抓每个取值组的根取值（父取值为NULL，对应属性树的根节点取值）
    SELECT 
        value_id,
        user_id,
        attribute_id,
        value,
        parent_value_id,
        ARRAY[value_id] AS value_path, -- 记录取值组的完整路径
        -- 提前关联属性路径，方便后续匹配
        (SELECT attribute_path FROM recursive_attribute_hierarchy rah WHERE rah.attribute_id = uav.attribute_id) AS attr_path_for_value,
        1 AS level
    FROM user_attribute_values uav
    WHERE parent_value_id IS NULL

    UNION ALL

    -- 递归：子取值关联父取值，拼接取值路径
    SELECT 
        uav.value_id,
        uav.user_id,
        uav.attribute_id,
        uav.value,
        uav.parent_value_id,
        ruv.value_path || uav.value_id AS value_path,
        (SELECT attribute_path FROM recursive_attribute_hierarchy rah WHERE rah.attribute_id = uav.attribute_id) AS attr_path_for_value,
        ruv.level + 1 AS level
    FROM user_attribute_values uav
    JOIN recursive_user_values ruv ON uav.parent_value_id = ruv.value_id
)
SELECT * FROM recursive_user_values;

步骤3：关联两张递归结果，捕获用户的属性及祖先属性取值

把上面两个递归结果关联，匹配用户ID、属性ID以及属性链的一致性，就能拿到指定用户的所有属性（包括祖先节点）的多组取值：

WITH recursive_attribute_hierarchy AS (
    SELECT 
        attribute_id,
        attribute_name,
        parent_attribute_id,
        ARRAY[attribute_id] AS attribute_path,
        1 AS level
    FROM attribute_hierarchy
    WHERE parent_attribute_id IS NULL

    UNION ALL

    SELECT 
        ah.attribute_id,
        ah.attribute_name,
        ah.parent_attribute_id,
        rah.attribute_path || ah.attribute_id AS attribute_path,
        rah.level + 1 AS level
    FROM attribute_hierarchy ah
    JOIN recursive_attribute_hierarchy rah ON ah.parent_attribute_id = rah.attribute_id
),
recursive_user_values AS (
    SELECT 
        value_id,
        user_id,
        attribute_id,
        value,
        parent_value_id,
        ARRAY[value_id] AS value_path,
        (SELECT attribute_path FROM recursive_attribute_hierarchy rah WHERE rah.attribute_id = uav.attribute_id) AS attr_path_for_value,
        1 AS level
    FROM user_attribute_values uav
    WHERE parent_value_id IS NULL

    UNION ALL

    SELECT 
        uav.value_id,
        uav.user_id,
        uav.attribute_id,
        uav.value,
        uav.parent_value_id,
        ruv.value_path || uav.value_id AS value_path,
        (SELECT attribute_path FROM recursive_attribute_hierarchy rah WHERE rah.attribute_id = uav.attribute_id) AS attr_path_for_value,
        ruv.level + 1 AS level
    FROM user_attribute_values uav
    JOIN recursive_user_values ruv ON uav.parent_value_id = ruv.value_id
)
SELECT 
    ru.user_id,
    rah.attribute_name,
    ru.value,
    rah.attribute_path AS 完整属性链,
    ru.value_path AS 对应取值组链
FROM recursive_user_values ru
JOIN recursive_attribute_hierarchy rah 
    ON ru.user_id = ru.user_id
    AND rah.attribute_id = ru.attribute_id
    AND rah.attribute_path = ru.attr_path_for_value
WHERE ru.user_id = 123; -- 替换成你要查询的用户ID

3. 关键注意事项 #

• 多组取值区分：通过value_path可以清晰区分同一用户的不同取值组，比如同一个属性在不同组有不同取值时，value_path会标记它属于哪一组的完整链。
• 性能优化：如果数据量较大，建议给attribute_hierarchy.parent_attribute_id、user_attribute_values.parent_value_id和user_attribute_values.user_id建立索引，能大幅提升递归CTE的查询速度。
• 灵活扩展：如果需要支持取值的生效时间、状态等，直接在user_attribute_values中添加字段，查询时增加过滤条件即可。

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