如何匹配两表中首个未被使用的ID与Value匹配的记录？

现有两个结构完全相同的表，表结构定义如下：

CREATE TABLE Table1 
(
    Id INT, 
    Name VARCHAR(256), 
    Value DECIMAL(18,2), 
    LineId INT
); 

CREATE TABLE Table2 
(
    Id INT, 
    Name VARCHAR(256), 
    Value DECIMAL(18,2), 
    LineId INT
); 

是否存在方法，仅匹配两表中首个“未被使用”的、满足Table1.ID = Table2.Id AND Table1.Value = Table2.Value条件的记录？

示例数据如下：

INSERT INTO Table1 VALUES(1, 'aaaaa', 100, 1);
INSERT INTO Table1 VALUES(1, 'bbbb', 50, 2);
INSERT INTO Table1 VALUES(1, 'cccc', 100, 3);
INSERT INTO Table1 VALUES(1, 'dddd', 5, 4);
INSERT INTO Table1 VALUES(1, 'ffff', 100, 5);

INSERT INTO Table2 VALUES(1, 'ddd', 40, 1);
INSERT INTO Table2 VALUES(1, 'eee', 100, 2);
INSERT INTO Table2 VALUES (1, 'ffff', 5, 3);
INSERT INTO Table2 VALUES(1, 'fff', 100, 4);

期望得到的结果为：

(1, 'aaaaa', 100 , 'eee',1);
(1, 'bbbb', 50, null,2);
(1, 'cccc', 100, 'fff',3);
(1, 'dddd', 5, 'ffff',4);
(1, 'ffff', 100, null,5);

我曾尝试使用LEFT JOIN实现，但产生了笛卡尔积，具体SQL语句如下：

SELECT
    t1.Id AS id, 
    t1.Name AS NameT1, 
    t1.Value AS ValueT1, 
    t2.Name AS fromName,
    t1.LineID AS LineId
FROM
    Table1 t1
LEFT JOIN
    Table2 t2 ON t1.Id = t2.Id AND t1.Value = t2.Value
ORDER BY
    t1.LineId;

要实现“匹配首个未被使用的记录”，核心是给每个符合匹配条件的分组内的记录添加序号，按序号一一匹配，避免重复使用同一Table2的记录。可以用窗口函数ROW_NUMBER()实现：

具体SQL语句 #

WITH T1_Ranked AS (
    SELECT 
        Id, 
        Name AS NameT1, 
        Value AS ValueT1, 
        LineId,
        ROW_NUMBER() OVER (PARTITION BY Id, Value ORDER BY LineId) AS MatchRank
    FROM Table1
),
T2_Ranked AS (
    SELECT 
        Id, 
        Name AS NameT2, 
        Value,
        ROW_NUMBER() OVER (PARTITION BY Id, Value ORDER BY LineId) AS MatchRank
    FROM Table2
)
SELECT 
    t1.Id,
    t1.NameT1,
    t1.ValueT1,
    t2.NameT2 AS fromName,
    t1.LineId
FROM T1_Ranked t1
LEFT JOIN T2_Ranked t2 
    ON t1.Id = t2.Id 
    AND t1.ValueT1 = t2.Value 
    AND t1.MatchRank = t2.MatchRank
ORDER BY t1.LineId;

结果说明 #

执行后会得到符合预期的结果：

原理说明 #

• PARTITION BY Id, Value：将数据按匹配条件分组，确保序号只在相同Id+Value的组内生成；
• ORDER BY LineId：按LineId顺序生成序号，保证“首个未被使用”的匹配逻辑；
• 通过MatchRank关联，让每个Table1的记录仅匹配同组同序号的Table2记录，彻底避免笛卡尔积问题。

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