SQL实现X与Y列的唯一配对：值仅可使用一次

要解决这个问题，核心是要在保证每个X和Y仅出现一次的前提下，优先选择原表中首次出现的有效组合，同时忽略无法匹配的剩余值（比如示例中的Y=3）。下面是具体的实现思路和代码：

问题分析 #

我们需要的是一种「贪心匹配」逻辑：

• 每个X只能和一个Y配对，每个Y也只能被一个X使用
• 优先选择原表中最早出现的X-Y组合
• 若某个Y没有可用的未配对X（比如示例中的Y=3，对应的X已经和其他Y配对了），则直接忽略该Y

解决方案代码 #

WITH ranked_pairs AS (
    -- 第一步：提取所有唯一的X-Y组合，并按它们首次出现的顺序排序
    SELECT 
        X, 
        Y,
        -- 按组合首次出现的位置排序，生成pair_rank
        ROW_NUMBER() OVER (ORDER BY MIN(ROWID)) AS pair_rank
    FROM T
    GROUP BY X, Y  -- 去重，同一个X-Y组合只保留一次
),
assigned AS (
    -- 第二步：给每个X的组合按出现顺序编号，给每个Y的组合按出现顺序编号
    SELECT 
        X, 
        Y, 
        pair_rank,
        -- 每个X下的组合按pair_rank排号，第一个组合为1
        ROW_NUMBER() OVER (PARTITION BY X ORDER BY pair_rank) AS x_seq,
        -- 每个Y下的组合按pair_rank排号，第一个组合为1
        ROW_NUMBER() OVER (PARTITION BY Y ORDER BY pair_rank) AS y_seq
    FROM ranked_pairs
)
-- 第三步：只选择每个X的第一个组合，且该组合是对应Y的第一个组合（保证X和Y都只被用一次）
SELECT X, Y
FROM assigned
WHERE x_seq = 1 AND y_seq = 1
ORDER BY pair_rank;

代码解释 #

1. ranked_pairs CTE：

   • 先对原表的X-Y组合去重，避免重复处理同一个组合
   • 用MIN(ROWID)找到每个组合首次出现的位置，然后按这个位置排序生成pair_rank，确保我们优先处理最早出现的组合

2. assigned CTE：

   • x_seq：给每个X的所有组合按pair_rank编号，每个X的第一个组合（最早出现的）编号为1
   • y_seq：给每个Y的所有组合按pair_rank编号，每个Y的第一个组合（最早出现的）编号为1

3. 最终查询：

   • 筛选x_seq=1（每个X只选第一个组合）且y_seq=1（每个Y只被第一个X选走）的记录，这样就保证了每个X和Y仅出现一次
   • 按pair_rank排序，保留原表的顺序

验证示例 #

对于你提供的输入表，执行这段代码后会得到完全符合期望的输出：

X Y
-----
A 1
B 2
C 4
D 5
E 6

Y=3因为对应的X（A、B）已经和其他Y配对，没有剩余的可用X，所以被自动忽略。

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