如何通过SQL查询多对多关系中所有唯一的多值组合？

这个问题其实很常见——当你需要提取多对多关系中唯一的集合组合时，硬写自连接显然不现实，毕竟你没法提前知道每个用户最多有多少个偏好颜色。核心思路是：先把每个用户的所有偏好颜色聚合为一个统一的表示（比如有序字符串或数组），再对这些表示去重，就能得到所有唯一的组合。

核心逻辑 #

1. 按person_id分组，把每个用户的preferred_color聚合为有序的单一值（排序是为了让相同颜色组合的表示一致，比如BLUE, RED和RED, BLUE会被识别为同一个组合）。
2. 对聚合后的结果去重，得到所有唯一的颜色组合。

主流数据库的具体实现 #

PostgreSQL #

PostgreSQL支持数组和字符串两种聚合方式，按需选择：

-- 方式1：返回数组格式的组合
SELECT DISTINCT ARRAY_AGG(preferred_color ORDER BY preferred_color) AS color_combination
FROM your_table
GROUP BY person_id;

-- 方式2：返回逗号分隔的字符串格式，更贴近你的示例输出
SELECT DISTINCT STRING_AGG(preferred_color, ', ' ORDER BY preferred_color) AS color_combination
FROM your_table
GROUP BY person_id;

-- 可选：输出带括号的格式（和你的示例完全匹配）
SELECT DISTINCT '(' || STRING_AGG(preferred_color, ', ' ORDER BY preferred_color) || ')' AS color_combination
FROM your_table
GROUP BY person_id;

MySQL 8.0+ #

使用GROUP_CONCAT实现字符串聚合，注意要加上排序保证组合一致性：

-- 返回逗号分隔的字符串
SELECT DISTINCT GROUP_CONCAT(preferred_color ORDER BY preferred_color SEPARATOR ', ') AS color_combination
FROM your_table
GROUP BY person_id;

-- 可选：输出带括号的格式
SELECT DISTINCT CONCAT('(', GROUP_CONCAT(preferred_color ORDER BY preferred_color SEPARATOR ', '), ')') AS color_combination
FROM your_table
GROUP BY person_id;

SQL Server 2017+ #

使用STRING_AGG配合WITHIN GROUP指定排序：

-- 返回逗号分隔的字符串
SELECT DISTINCT STRING_AGG(preferred_color, ', ') WITHIN GROUP (ORDER BY preferred_color) AS color_combination
FROM your_table
GROUP BY person_id;

-- 可选：输出带括号的格式
SELECT DISTINCT CONCAT('(', STRING_AGG(preferred_color, ', ') WITHIN GROUP (ORDER BY preferred_color), ')') AS color_combination
FROM your_table
GROUP BY person_id;

为什么这个方法比自连接好？ #

自连接的局限性非常明显：你必须提前知道最大的颜色数量，比如有用户有3种颜色就要自连接2次，有10种颜色就要自连接9次，代码会极度冗余且无法适配动态变化的数据。而聚合的方法不管每个用户有1种还是N种颜色，都能一次性处理，灵活且简洁。

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