MySQL多表关联如何实现各列无重复值 同颜色仅匹配单条记录

问题原因 #

直接使用DISTINCT无法满足需求的核心原因是：DISTINCT只会对SELECT后声明的所有字段的整行组合做去重，只要任意字段值不同，就会判定为不同行。
本次场景中：

• 表1内yellow对应id_1=2、id_1=4两条记录
• 表2内yellow对应cloth_id=104、cloth_id=105两条记录
  直接JOIN加DISTINCT会产生多组不完全重复的行，无法实现「所有列无重复、每种颜色仅匹配1个cloth_id」的要求。

可用SQL写法 #

写法1：通用聚合写法（兼容所有MySQL版本） #

核心逻辑是先分别对两张表按颜色维度聚合，每个颜色只保留1条符合规则的记录，再做关联。和示例结果对齐的规则为：同颜色下取最小的id_1、同颜色下取最小的cloth_id。

SELECT
  t1_unique.id_1,
  t2_unique.cloth_id
FROM (
  -- 表1按颜色去重，每个颜色保留最小id_1
  SELECT
    MIN(id_1) AS id_1,
    favourite_color
  FROM table1
  GROUP BY favourite_color
) t1_unique
INNER JOIN (
  -- 表2按颜色去重，每个颜色保留最小cloth_id
  SELECT
    color,
    MIN(cloth_id) AS cloth_id
  FROM table2
  GROUP BY color
) t2_unique
ON t1_unique.favourite_color = t2_unique.color
ORDER BY t1_unique.id_1;

执行结果和预期完全一致：

写法2：窗口函数写法（支持MySQL 8.0及以上版本） #

如果使用8.0及以上版本MySQL，可以用ROW_NUMBER()窗口函数实现分组排序取唯一值，扩展性更强，调整匹配规则时只需要修改排序逻辑即可。

WITH t1_unique AS (
  SELECT
    id_1,
    favourite_color,
    ROW_NUMBER() OVER (PARTITION BY favourite_color ORDER BY id_1 ASC) AS rn
  FROM table1
),
t2_unique AS (
  SELECT
    color,
    cloth_id,
    ROW_NUMBER() OVER (PARTITION BY color ORDER BY cloth_id ASC) AS rn
  FROM table2
)
SELECT
  t1.id_1,
  t2.cloth_id
FROM t1_unique t1
INNER JOIN t2_unique t2
  ON t1.favourite_color = t2.color
  AND t1.rn = 1
  AND t2.rn = 1
ORDER BY t1.id_1;

提示：如果需要匹配同颜色下其他记录，比如取最大的cloth_id，只需要把对应子句里的ORDER BY cloth_id ASC改为ORDER BY cloth_id DESC即可。

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