合并两个具有精确数据分布的表

Merge Two Tables with Exact Data Distribution

提问人：Luiz 提问时间：11/3/2023 最后编辑：Dale KLuiz 更新时间：11/3/2023 访问量：48

问：

我正在使用 SQL Server。

我需要根据“Ctrl”列连接两个表。第一个表“Cad_Clients”包含客户端信息，而第二个表“Cad_Colors”包含信息说明。连接它们的条件是两个表中的“Ctrl”值必须相同。但是，分布需要精确，这意味着不会发生重复。

想象一下，我在“Cad_Colors”表中创建了一个“for”循环。在每次迭代中，只要“Ctrl”值匹配，我就会将表中的记录添加到“Cad_Clients”。之后，我从“Cad_Colors”中删除了该记录。如果循环表变为空或循环到达其末尾，则意味着我已经分发了尽可能多的记录。仅此而已，但我不想在数据库中使用循环;我相信，通过在这里讨论它，我们可以获得更简洁的结果。

问题的视觉描述：

Visual depiction of the issue

Cad_Clients

| idPerson | Person         | Ctrl  |
|----------|----------------|-------|
| 1        | John           | 100   |
| 2        | Sarah          | 101   |
| 3        | Michael        | 102   |
| 4        | Emily          | 103   |
| 5        | William        | 104   |
| 6        | Olivia         | 105   |
| 7        | James          | 100   |
| 8        | Emma           | 101   |
| 9        | Benjamin       | 102   |
| 10       | Sophia         | 103   |
| 11       | Samuel         | 104   |
| 12       | Ava            | 105   |
| 13       | Joseph         | 100   |

Cad_Colors

| Ctrl | Color    | idColor |
|------|----------|---------|
| 100  | Red      | 1       |
| 104  | Blue     | 2       |
| 105  | Green    | 3       |
| 103  | Yellow   | 4       |
| 104  | Purple   | 5       |
| 105  | Red      | 6       |

合并两个表：

| idPerson | Person  | Ctrl | Color  |
|----------|---------|------|--------|
| 1        | John    | 100  | Red    |
| 4        | Emily   | 103  | Yellow |
| 5        | William | 104  | Blue   |
| 6        | Olivia  | 105  | Green  |
| 11       | Samuel  | 104  | Purple |
| 12       | Ava     | 105  | Red    |

一些不成功的尝试：

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

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

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)

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)*/ );
sql-server 联接 合并

评论

0赞 Joel Coehoorn 11/3/2023
颜色总是比客户短吗，每一种颜色总能找到客户吗？
0赞 Luiz 11/3/2023
并非总是如此！颜色的数量可能足以满足所有客户端的需求（如果满足 Ctrl 条件）。客户端表也可能小于颜色表。记录的数量无关紧要;真正重要的是，正如我所提到的，它们是分布式的。
0赞 Joel Coehoorn 11/3/2023
不幸的是，“MERGE”实际上并不是关系代数运算，所以这将是困难的。我已经接近了，但它并没有消耗客户端：dbfiddle.uk/KCzhhrhY

答：

2赞 siggemannen 11/3/2023 #1

这应该是一个简单的ROW_NUMBER联接：

WITH persons AS (
    SELECT  *
    FROM    (
        VALUES  (1, N'John', 100)
        ,   (2, N'Sarah', 101)
        ,   (3, N'Michael', 102)
        ,   (4, N'Emily', 103)
        ,   (5, N'William', 104)
        ,   (6, N'Olivia', 105)
        ,   (7, N'James', 100)
        ,   (8, N'Emma', 101)
        ,   (9, N'Benjamin', 102)
        ,   (10, N'Sophia', 103)
        ,   (11, N'Samuel', 104)
        ,   (12, N'Ava', 105)
        ,   (13, N'Joseph', 100)
    ) t (idPerson,Person,Ctrl)
)
, colors AS (
    SELECT  *
    FROM
    (
        VALUES  (100, N'Red', 1)
        ,   (104, N'Blue', 2)
        ,   (105, N'Green', 3)
        ,   (103, N'Yellow', 4)
        ,   (104, N'Purple', 5)
        ,   (105, N'Red', 6)
    ) t (Ctrl,Color,idColor)
)
SELECT  p.idPerson, p.Person, p.Ctrl, c.Color
FROM    (
    SELECT  *
    ,   ROW_NUMBER() OVER(partition BY ctrl ORDER BY idperson) AS sort
    FROM    persons c
    ) p
INNER JOIN (
    SELECT  *
    ,   ROW_NUMBER() OVER(partition BY ctrl ORDER BY idcolor) AS sort
    FROM    colors
    ) c
    ON  c.Ctrl = p.Ctrl
    AND c.sort = p.sort

您可以为按 Ctrl 喜欢分组的每个人创建一个计数器，并对颜色执行相同的操作，然后将它们连接在一起。ROW_NUMBER() OVER(partition BY ctrl ORDER BY idperson)

输出：

idPerson的 人 Ctrl 组合键 颜色
1 John 100 红
4 艾米丽 103 黄色
5 威廉 104 蓝
11 塞缪尔 104 紫色
6 奥利维亚 105 绿
12 艾娃 105 红

评论

0赞 Luiz 11/3/2023
哇！我不知道这个Row_Number功能。在连接两个表的条件中创建唯一的关联 ID 真的很酷。感谢您的帮助，说真的，我刚刚开始使用 SQL Server，我有点迷茫。
0赞 siggemannen 11/3/2023
是的，不久前添加时，它改变了游戏规则！很高兴它对你有用。顺便说一句，如果答案有帮助，您可以通过单击某个按钮来“批准它”，因此它变为绿色
-1赞 Saikat 11/3/2023 #2

这是解决方案。您可以使用row_number窗口函数来实现这一点。

select 
        a.idPerson , 
        a.Person ,
        a.Ctrl , 
        b.Color 
    from
    (select * , row_number () over(partition by Ctrl order by idPerson) as cad_person_seq from Cad_Clients) 
    as a inner join
    (select * , row_number () over(partition by Ctrl order by idColor) as cad_color_seq from Cad_Colors) as b 
    on a.Ctrl = b.Ctrl and a.cad_person_seq = b.cad_color_seq
    order by a.cad_person_seq , a.Ctrl;

上一个：联接多个表以显示整个数据，而无需交叉联接

下一个：用于检查房屋结构中是否存在 ID 的查询