获取customers表多手机号列重复值分组记录的PSQL查询

解决方案代码 #

SELECT ARRAY_AGG(id_groups) AS result
FROM (
    SELECT ARRAY_AGG(id ORDER BY id) AS id_groups
    FROM (
        SELECT id, UNNEST(ARRAY[mobile, mobile2, mobile3, mobile4]) AS phone
        FROM customers
    ) AS unpivoted
    WHERE phone IS NOT NULL AND phone != ''
    GROUP BY phone
) AS grouped;

代码说明 #

• 行转列处理：通过UNNEST(ARRAY[mobile, mobile2, mobile3, mobile4])把每条记录的4个手机号列拆为单独行，同时保留对应客户ID，让每个手机号与ID的对应关系变成单行数据。
• 过滤无效值：用WHERE phone IS NOT NULL AND phone != ''排除空手机号，避免空值被当作相同号码错误聚合。
• 按手机号分组聚合ID：内层GROUP BY phone将拥有相同手机号的客户ID用ARRAY_AGG(id ORDER BY id)收集为有序数组。
• 最终聚合结果：外层ARRAY_AGG(id_groups)把所有分组的ID数组聚合成一个大数组，输出格式类似{{1,2,3},{4,5}}，和需求的[(1,2,3), (4,5)]逻辑一致。

如果需要严格匹配[(1,2,3), (4,5)]的字符串格式，可使用调整后的代码：

SELECT '[' || STRING_AGG('(' || ARRAY_TO_STRING(id_groups, ',') || ')', ', ') || ']' AS result
FROM (
    SELECT ARRAY_AGG(id ORDER BY id) AS id_groups
    FROM (
        SELECT id, UNNEST(ARRAY[mobile, mobile2, mobile3, mobile4]) AS phone
        FROM customers
    ) AS unpivoted
    WHERE phone IS NOT NULL AND phone != ''
    GROUP BY phone
) AS grouped;

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