PostgreSQL中如何从A1数组移除A2数组元素生成新列A3

假设你使用的数据库支持数组相关函数（如string_to_array、UNNEST、ARRAY_AGG），可以通过以下步骤实现需求：

1. 核心逻辑拆解 #

• 将逗号分隔的字符串转换为数组：用string_to_array把A1、A2转成数组，同时处理A2为空的情况（空字符串/NULL转空数组）
• 展开A1的数组，筛选出不在A2数组中的元素
• 按ID分组，将筛选后的元素重新聚合为逗号分隔的字符串，同时保留A1原有的元素顺序

2. 完整SQL语句 #

-- 生成A3列的查询
SELECT
    ID,
    A1,
    A2,
    CASE
        WHEN A2 = '' OR A2 IS NULL THEN A1
        ELSE array_to_string(
            ARRAY_AGG(elem ORDER BY pos), -- 严格保持A1的元素顺序
            ','
        )
    END AS A3
FROM (
    -- 展开A1数组并记录元素原始位置
    SELECT
        ID,
        A1,
        A2,
        string_to_array(A1, ',') AS a1_array,
        string_to_array(COALESCE(A2, ''), ',') AS a2_array,
        elem,
        pos
    FROM t,
         UNNEST(string_to_array(A1, ',')) WITH ORDINALITY AS u(elem, pos)
) sub
-- 筛选出不在A2数组中的元素
WHERE NOT elem = ANY(a2_array)
GROUP BY ID, A1, A2
-- 补充A2为空的行（避免子查询过滤掉这类数据）
UNION ALL
SELECT
    ID,
    A1,
    A2,
    A1 AS A3
FROM t
WHERE A2 = '' OR A2 IS NULL;

3. 关键函数说明 #

• string_to_array(str, ',')：将逗号分隔的字符串转为数组，例如'x,y,z'转成['x','y','z']
• UNNEST(arr) WITH ORDINALITY：展开数组并返回元素的原始位置（pos），保证聚合后元素顺序和A1一致
• elem = ANY(a2_array)：判断元素是否存在于A2数组中，NOT取反即可保留需要的元素
• ARRAY_AGG(elem ORDER BY pos)：按原始位置聚合元素，维持原有顺序
• array_to_string(arr, ',')：将数组重新转为逗号分隔的字符串
• COALESCE(A2, '')：处理A2为NULL的场景，避免string_to_array返回NULL数组

4. 简化版本（无需严格保留顺序） #

如果不需要维持A1的元素顺序，可以简化为：

SELECT
    ID,
    A1,
    A2,
    CASE
        WHEN A2 = '' OR A2 IS NULL THEN A1
        ELSE array_to_string(
            ARRAY(SELECT elem FROM UNNEST(string_to_array(A1, ',')) elem WHERE elem NOT IN (SELECT unnest(string_to_array(A2, ',')))),
            ','
        )
    END AS A3
FROM t;

如果使用BigQuery，函数略有差异，可参考以下语句：

SELECT
    ID,
    A1,
    A2,
    CASE
        WHEN A2 = '' OR A2 IS NULL THEN A1
        ELSE ARRAY_TO_STRING(
            ARRAY_AGG(elem ORDER BY pos),
            ','
        )
    END AS A3
FROM (
    SELECT
        ID,
        A1,
        A2,
        SPLIT(A1, ',') AS a1_array,
        SPLIT(COALESCE(A2, ''), ',') AS a2_array,
        elem,
        pos
    FROM t,
         UNNEST(SPLIT(A1, ',')) WITH OFFSET pos
) sub
WHERE NOT elem IN UNNEST(a2_array)
GROUP BY ID, A1, A2
UNION ALL
SELECT
    ID,
    A1,
    A2,
    A1 AS A3
FROM t
WHERE A2 = '' OR A2 IS NULL;

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