PostgreSQL中比较两表逗号分隔列（顺序无关且需完全匹配）

我来帮你搞定这个问题！你现在遇到的核心卡点在于：直接用数组=比较会严格校验元素顺序，而你需要的是不管顺序、元素必须完全一致的匹配——既不能多元素也不能少元素，同时忽略原字符串里的顺序。

为什么你的原查询没结果？ #

你用regexp_split_to_array把字符串转成数组后，'A,B,C,D,E'和'B,A,C,E,D'生成的数组顺序是不一样的，PostgreSQL里数组的=是严格按位置比较的，所以这两个数组自然不相等，也就查不到结果。而&&运算符是判断两个数组是否有交集（或者说是否存在共同元素），它不会检查元素是否完全一致，所以会把id=2这种子集情况也返回，不符合你的要求。

解决方案：排序后再比较 #

我们可以把两个数组排序后再进行比较——只要元素完全一致，不管原顺序如何，排序后的数组结构肯定是一样的。

方法1：用array_sort（PostgreSQL 13+ 支持）

这是最简洁的方式，直接对拆分后的数组排序再比较：

SELECT 
    a.id1, 
    b.id1, 
    a.shared, 
    b.shared 
FROM x_a a 
JOIN x_b b ON a.id1 = b.id1 
WHERE array_sort(regexp_split_to_array(LOWER(a.shared), ',')) = array_sort(regexp_split_to_array(LOWER(b.shared), ','));

• LOWER()是为了做大小写不敏感的匹配，如果你的业务不需要，可以去掉
• array_sort()会把数组元素按字典序排序，这样顺序不同但元素相同的数组就会变成完全一致的结构
• 这个查询会只返回id1=1的记录，因为它的元素完全匹配；id1=2的记录因为元素数量不同，排序后的数组长度不一样，不会被匹配，正好符合你的需求。

方法2：兼容低版本PostgreSQL（12及以下）

如果你的PostgreSQL版本低于13，没有array_sort函数，可以用unnest+string_agg的组合，把数组拆成元素后排序再拼接成字符串，最后比较字符串：

SELECT 
    a.id1, 
    b.id1, 
    a.shared, 
    b.shared 
FROM x_a a 
JOIN x_b b ON a.id1 = b.id1 
WHERE (
    -- 把a的shared列转成排序后的字符串
    SELECT string_agg(elem, ',' ORDER BY elem) 
    FROM unnest(regexp_split_to_array(LOWER(a.shared), ',')) elem
) = (
    -- 把b的shared列转成排序后的字符串
    SELECT string_agg(elem, ',' ORDER BY elem) 
    FROM unnest(regexp_split_to_array(LOWER(b.shared), ',')) elem
);

如果你的业务场景允许元素重复（比如'A,A,B'和'A,B,A'需要被判定为匹配），这个方法完全适用；如果需要去重后匹配，就在string_agg里加上DISTINCT即可：string_agg(DISTINCT elem, ',' ORDER BY elem)。

验证结果 #

运行上面的查询后，你会得到唯一的结果：

id1=2的记录因为元素数量不同，不会被匹配，完美符合你的要求。

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