Como Unir Duas Tabelas com Distribuição Exata de Dados no SQL Server?

Programação
, , ,
#1

Olá! Estou trabalhando com SQL Server.

Preciso unir duas tabelas com base na coluna “Ctrl”. A primeira tabela, “Cad_Clients”, contém informações de clientes, enquanto a segunda, “Cad_Colors”, possui a descrição das informações. A condição para uni-las é que o valor de “Ctrl” seja o mesmo em ambas as tabelas. No entanto, a distribuição precisa ser exata, ou seja, não podem ocorrer duplicatas.

Imagine que eu crie um laço ‘for’ na tabela ´Cad_Colors´. Em cada iteração, adiciono um registro da tabela à ‘Cad_Clients’, desde que o valor ‘Ctrl’ corresponda. Após, excluo esse registro de ´Cad_Colors´. Se a tabela em loop ficar vazia ou o laço chegar ao fim, significa que distribuí o máximo possível dos registros. É apenas isso, mas não quero usar um laço no banco; estou confiante de que, discutindo aqui, podemos obter um resultado muito mais conciso.

Representação visual do problema

table
table1210×562 35.3 KB

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       |

União das duas tabelas:

| 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    |

Algumas tentativas frustradas:

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;

Ajuda para criar os cenários:

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)*/ );
1 curtida
#2

Já ontem te tinha indicado o caminho para o ROW_NUMBER…

select cl.[idPerson], cl.[Person], cl.[Ctrl], co.[Color], co.[idColor]
  from (select t.*, ROW_NUMBER() OVER (PARTITION BY [Ctrl] ORDER BY [idPerson]) [cnt] 
          from Cad_Clients t) cl
 inner join (select t.*, ROW_NUMBER() OVER (PARTITION BY [Ctrl] ORDER BY [idColor]) [cnt] 
               from Cad_Colors t) co
         on cl.[Ctrl] = co.[Ctrl] and cl.[Cnt] = co.[Cnt]
 order by cl.[idPerson]
2 curtidas
#3

Obrigado pela ajuda :smile: Era exatamente isso que eu precisava, não conhecia a função. Aliás, sinto não ter respondido seu comentário no outro post. Precisei excluí-lo poque estava fora dos padrões da comunidade. Acho que consegui sintetizar melhor o problema aqui :laughing: