如何在多列上使用LISTAGG并去除重复值

你当前的写法错误在于：SALES_CTRY_LIST和DP_CTRY_LIST本身是已聚合完成的逗号分隔字符串，直接拼接后用LISTAGG(DISTINCT ...)只会对整个拼接后的字符串去重，无法拆分字符串内部的元素去重。比如SALES_CTRY_LIST = 'CN, US'、DP_CTRY_LIST = 'US, JP'，拼接后是'CN, US, US, JP'，LISTAGG无法识别内部重复的US。

方案1：拆分元素→去重→重新聚合（推荐） #

先把两个字符串列表拆分成单个国家元素，去重后再用LISTAGG合并。以Oracle为例（不同数据库拆分函数略有差异）：

如果是全局合并所有行的两个列表：

WITH split_countries AS (
    -- 拆分销售国家列表
    SELECT DISTINCT TRIM(REGEXP_SUBSTR(SALES_CTRY_LIST, '[^,]+', 1, LEVEL)) AS country
    FROM PLA
    WHERE SALES_CTRY_LIST IS NOT NULL
    CONNECT BY REGEXP_SUBSTR(SALES_CTRY_LIST, '[^,]+', 1, LEVEL) IS NOT NULL
    UNION
    -- 拆分DP国家列表
    SELECT DISTINCT TRIM(REGEXP_SUBSTR(DP_CTRY_LIST, '[^,]+', 1, LEVEL)) AS country
    FROM PLA
    WHERE DP_CTRY_LIST IS NOT NULL
    CONNECT BY REGEXP_SUBSTR(DP_CTRY_LIST, '[^,]+', 1, LEVEL) IS NOT NULL
)
SELECT LISTAGG(country, ', ') WITHIN GROUP (ORDER BY country) AS TESTING
FROM split_countries;

如果是按行处理（比如每个主键对应的数据合并去重）：

WITH split_rows AS (
    SELECT 
        PLA.id, -- 替换为你的行唯一标识字段
        TRIM(REGEXP_SUBSTR(PLA.SALES_CTRY_LIST, '[^,]+', 1, LEVEL)) AS country
    FROM PLA
    WHERE PLA.SALES_CTRY_LIST IS NOT NULL
    CONNECT BY 
        REGEXP_SUBSTR(PLA.SALES_CTRY_LIST, '[^,]+', 1, LEVEL) IS NOT NULL
        AND PRIOR PLA.id = PLA.id
        AND PRIOR SYS_GUID() IS NOT NULL -- 防止递归循环
    UNION
    SELECT 
        PLA.id,
        TRIM(REGEXP_SUBSTR(PLA.DP_CTRY_LIST, '[^,]+', 1, LEVEL)) AS country
    FROM PLA
    WHERE PLA.DP_CTRY_LIST IS NOT NULL
    CONNECT BY 
        REGEXP_SUBSTR(PLA.DP_CTRY_LIST, '[^,]+', 1, LEVEL) IS NOT NULL
        AND PRIOR PLA.id = PLA.id
        AND PRIOR SYS_GUID() IS NOT NULL
)
SELECT 
    id,
    LISTAGG(DISTINCT country, ', ') WITHIN GROUP (ORDER BY country) AS TESTING
FROM split_rows
GROUP BY id;

方案2：正则去重（仅适合简单场景） #

如果不想拆分，可尝试用正则去除连续重复元素，但无法处理非连续重复，仅作临时方案：

CASE 
    WHEN PLA.SALES_CTRY_LIST IS NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
        THEN PLA.DP_CTRY_LIST 
    WHEN PLA.DP_CTRY_LIST IS NULL AND PLA.SALES_CTRY_LIST IS NOT NULL 
        THEN PLA.SALES_CTRY_LIST 
    WHEN PLA.SALES_CTRY_LIST IS NOT NULL AND PLA.DP_CTRY_LIST IS NOT NULL 
        THEN REGEXP_REPLACE(
            PLA.SALES_CTRY_LIST || ', ' || PLA.DP_CTRY_LIST,
            '([^,]+)(, \1)+',
            '\1'
        )
END AS "TESTING"

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