SQL Server中两表按Ctrl列精准关联（无重复分配）

使用SQL Server，需基于Ctrl列关联Cad_Clients（客户信息表）和Cad_Colors（信息描述表），关联条件为两表Ctrl值相同。要求精准分配：Cad_Colors中的每条记录只能被匹配一次，不可重复使用。

可理解为：Cad_Colors的每条记录匹配到对应Ctrl的客户后即被"消耗"，直到表中无可用记录或无匹配客户。需避免使用数据库循环，寻求简洁实现方式。

Cad_Clients表 #

Cad_Colors表 #

期望关联结果 #

尝试过但未成功的SQL语句 #

语句1 #

INSERT INTO @Tmp
SELECT ccl.idPerson, ccl.Person, ccl.Ctrl, 
(SELECT TOP 1 cco.idColor FROM Cad_Colors cco WHERE cco.Ctrl = ccl.Ctrl AND cco.idColor NOT IN(SELECT idColor FROM @Tmp))
FROM Cad_Clients ccl

语句2 #

INSERT INTO @Tmp
SELECT ccl.idPerson, ccl.Person, ccl.Ctrl, cco.idColor, cco.Color
FROM Cad_Clients ccl
JOIN (SELECT TOP 1 cco.* FROM Cad_Colors cco WHERE cco.idColor NOT IN(SELECT idColor FROM @Tmp)) cco ON cco.Ctrl = ccl.Ctrl

语句3 #

INSERT INTO @Tmp
SELECT ccl.idPerson, ccl.Person, ccl.Ctrl, cco.idColor, cco.Color
FROM Cad_Clients ccl
JOIN Cad_Colors cco ON cco.Ctrl = ccl.Ctrl
WHERE NOT EXISTS (SELECT 1 FROM @Tmp WHERE Ctrl = cco.Ctrl)

语句4 #

MERGE INTO Cad_Clients AS target
USING Cad_Colors AS source
ON target.Ctrl = source.Ctrl
WHEN MATCHED THEN
UPDATE SET target.idPerson = target.idPerson
OUTPUT inserted.idPerson, inserted.Person, inserted.Ctrl, inserted.idColor, inserted.Color INTO @tmp;

测试环境创建脚本 #

CREATE TABLE Cad_Clients( [idPerson] [int] IDENTITY(1,1) NOT NULL, [Person] [varchar](60) NULL, [Ctrl] [int] NULL );
CREATE TABLE Cad_Colors( [Ctrl] [int] NULL, [Color] [varchar](60) NULL, [idColor] [int] IDENTITY(1,1) NOT NULL );
INSERT INTO Cad_Clients (Person, Ctrl) VALUES ('John',100), ('Sarah',101), ('Michael',102), ('Emily',103), ('William',104), ('Olivia',105), ('James',100), ('Emma',101), ('Benjamin',102), ('Sophia',103), ('Samuel',104), ('Ava',105), ('Joseph',100);
INSERT INTO Cad_Colors (Ctrl, Color) VALUES (100, 'Red'), (104, 'Blue'), (105, 'Green'), (103, 'Yellow'), (104, 'Purple'), (105, 'Red');
DECLARE @Tmp TABLE( idPerson INT, Person VARCHAR(60), Ctrl INT, idColor INT /*Color VARCHAR(60)*/ );

通过**窗口函数ROW_NUMBER()**给两张表按Ctrl分组生成序号，再关联同Ctrl且序号一致的记录，即可实现Cad_Colors记录不重复匹配的需求，无需循环。

实现SQL #

WITH ClientRN AS (
    SELECT 
        idPerson,
        Person,
        Ctrl,
        -- 按Ctrl分组，给同组客户按idPerson排序生成序号
        ROW_NUMBER() OVER(PARTITION BY Ctrl ORDER BY idPerson) AS rn
    FROM Cad_Clients
),
ColorRN AS (
    SELECT 
        Ctrl,
        Color,
        idColor,
        -- 按Ctrl分组，给同组颜色记录按idColor排序生成序号
        ROW_NUMBER() OVER(PARTITION BY Ctrl ORDER BY idColor) AS rn
    FROM Cad_Colors
)
INSERT INTO @Tmp (idPerson, Person, Ctrl, idColor)
SELECT 
    crn.idPerson,
    crn.Person,
    crn.Ctrl,
    colrn.idColor
FROM ClientRN crn
JOIN ColorRN colrn 
    ON crn.Ctrl = colrn.Ctrl 
    AND crn.rn = colrn.rn;

-- 查询最终结果
SELECT t.idPerson, t.Person, t.Ctrl, cc.Color
FROM @Tmp t
JOIN Cad_Colors cc ON t.idColor = cc.idColor;

逻辑说明 #

1. ClientRN：给每个Ctrl分组的客户记录生成递增序号，确保同Ctrl的客户有唯一标识。
2. ColorRN：给每个Ctrl分组的颜色记录生成递增序号，确保同Ctrl的颜色记录有唯一标识。
3. 关联时同时匹配Ctrl和序号rn，保证每条颜色记录只会和同组同序号的客户记录关联，实现"一对一不重复"的精准分配。

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