PostgreSQL合并同类型下文本字段值被包含的行集

你写的SQL存在三个核心错误，导致无法满足需求：

1. 包含判断逻辑完全失效：一方面用整串LIKE做匹配无法避免子串误判（比如值Jo会被误判为在John中），另一方面你写的a.values LIKE '%'||a.values||'%'是恒真条件，根本没有关联比对b表的values字段；最关键的是你只做了单行feature的匹配，没有校验「某id下所有feature全部满足包含关系」这个全局条件，自然会误判id1和id5这类不满足要求的记录。
2. 冗余的更新逻辑：合并规则的触发前提就是被移除id的所有feature值都已经完全被保留id覆盖，不存在需要补充的新值，根本不需要执行UPDATE操作修改保留id的values，强行拼接值反而会出现重复问题。
3. 多值处理逻辑缺失：没有对分号分隔的多值做拆分、去重，直接拼接字符串必然出现重复值、匹配错误的问题。

• 先把分号分隔的多值字段拆分为独立值的集合，空值、空字符串统一按空集合处理，从根源避免字符串匹配的误判
• 对同一being类型下的所有id做两两全量比对，仅当id A的每一个feature对应的value集合，全部是id B同feature value集合的子集时，才标记A为待删除的被包含记录
• 直接删除所有被标记的id记录即可，不需要修改保留id的任何字段值

注意把代码中的your_table替换为你实际的表名：

WITH id_feature_values AS (
    -- 拆分每个(id, feature)对应的多值为去重数组，空值统一处理为空数组
    SELECT 
        id,
        being,
        feature,
        CASE 
            WHEN values IS NULL OR TRIM(values) = '' THEN ARRAY[]::TEXT[]
            ELSE ARRAY(SELECT DISTINCT TRIM(unnest(string_to_array(values, ';'))))
        END AS val_arr
    FROM your_table
),
id_all_features AS (
    -- 按id聚合所有属性，生成feature到值数组的映射，方便全局校验
    SELECT 
        id,
        being,
        jsonb_object_agg(feature, val_arr) AS feature_map
    FROM id_feature_values
    GROUP BY id, being
),
contain_relation AS (
    -- 两两比对同类型id，找出所有被其他id包含的记录
    SELECT 
        x.id AS contained_id
    FROM id_all_features x
    JOIN id_all_features y 
        ON x.being = y.being 
        AND x.id != y.id
    -- 核心校验：x不存在任何一个feature不被y包含
    WHERE NOT EXISTS (
        SELECT 1
        FROM jsonb_each(x.feature_map) AS x_feat(feat_name, x_vals)
        WHERE 
            -- y没有x持有的属性，直接不满足包含关系
            NOT y.feature_map ? x_feat_name
            -- y有对应属性，但x的值集合不是y值集合的子集
            OR NOT (y.feature_map->>x_feat_name)::TEXT[] @> x_vals::TEXT[]
    )
),
to_delete_ids AS (
    SELECT DISTINCT contained_id AS id FROM contain_relation
)
-- 执行删除操作
DELETE FROM your_table
WHERE id IN (SELECT id FROM to_delete_ids);

在你提供的样例数据上执行上述代码，结果完全符合预期：

• 仅id4被标记为待删除：id4的name值集合{John}是id1、id5同name集合的子集，surname为空集（是任意集合的子集），满足被包含条件
• id1和id5不会被误合并：id1的name集合含Paul不在id5的name中，id5的name集合含Ringo不在id1的name中，互不满足包含关系
• id2、id3不会被误合并：二者name值集合无包含关系
• 不会出现跨being类型的合并：比对逻辑仅在相同being的id间执行
• 不存在值重复问题：没有多余的UPDATE拼接逻辑，保留的记录完全是原始符合要求的数据

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