MySQL如何通过SQL将多对多关联表转为两列值均唯一的一对一表

核心思路：从多对多的关联矩阵中，抽取满足「两列值满足唯一约束、且关联关系真实存在」的一一对应集合，无需复杂的图匹配算法，通过窗口函数排序对齐的方式就能快速复现你提到的两种目标结果。

通用实现逻辑 #

整个转换过程只需要三个步骤：

• 分别对两个列的去重值做连续排序编号，保证每个唯一值对应一个不重复的序号
• 按相同序号关联两个列的编号结果，此时生成的配对天然满足两列值唯一的约束
• 增加过滤条件，只保留原始表中真实存在的关联对，避免生成无依据的假匹配

两种目标结果的SQL编写 #

生成tbl1正序配对结果 #

让两个列都按值升序排序后对齐匹配，刚好对应你给出的第一组目标数据：

WITH 
-- 对id1去重后按值升序排序编号
ranked_id1 AS (
    SELECT DISTINCT id1, ROW_NUMBER() OVER(ORDER BY id1) AS match_rank
    FROM Test
),
-- 对id2去重后按值升序排序编号
ranked_id2 AS (
    SELECT DISTINCT id2, ROW_NUMBER() OVER(ORDER BY id2) AS match_rank
    FROM Test
)
SELECT a.id1, b.id2
FROM ranked_id1 a
INNER JOIN ranked_id2 b 
    ON a.match_rank = b.match_rank
-- 仅保留原始表中真实存在的关联
WHERE EXISTS (
    SELECT 1 
    FROM Test t 
    WHERE t.id1 = a.id1 AND t.id2 = b.id2
);

在你的示例数据下，这段SQL的输出完全匹配tbl1的结构：

生成tbl2逆序配对结果 #

只需把id2的排序规则改为降序，再和id1的升序序号对齐，就能得到第二组目标结果：

WITH 
ranked_id1 AS (
    SELECT DISTINCT id1, ROW_NUMBER() OVER(ORDER BY id1) AS match_rank
    FROM Test
),
-- id2按值降序排序编号
ranked_id2 AS (
    SELECT DISTINCT id2, ROW_NUMBER() OVER(ORDER BY id2 DESC) AS match_rank
    FROM Test
)
SELECT a.id1, b.id2
FROM ranked_id1 a
INNER JOIN ranked_id2 b 
    ON a.match_rank = b.match_rank
WHERE EXISTS (
    SELECT 1 
    FROM Test t 
    WHERE t.id1 = a.id1 AND t.id2 = b.id2
);

在示例数据下，这段SQL的输出完全匹配tbl2的结构：

场景适配说明 #

• 如果需要生成随机的一对一匹配关系，只需把排序的ORDER BY条件替换为对应数据库的随机值生成函数即可：MySQL用ORDER BY RAND()、PostgreSQL用ORDER BY RANDOM()、SQL Server用ORDER BY NEWID()
• 当原始多对多关联无法实现完全匹配（比如两端唯一值数量不等、或关联路径不连通）时，EXISTS过滤会自动剔除无效配对，最终输出可实现的最大一对一关系集合
• 这种实现的计算复杂度远低于传统二分图最大匹配算法，覆盖绝大多数常规数据分析场景

内容的提问来源于stack exchange，提问作者S.Perera