Início / dba / Perguntas / 193404
Accepted
Thomas Franz
Asked: 2017-12-19 06:37:27 +0800 CST

Lista de IDs que têm uma atribuição para TODOS os outros IDs em outra tabela

  • 772

Preciso de ajuda com a seguinte tarefa (desistir depois de algumas horas)

CREATE TABLE #cities (cityId INT, city VARCHAR(100));
INSERT INTO #cities (cityId, city) 
VALUES (1, 'Metropolis A'),
       (2, 'Town B'),
       (3, 'Village C');

CREATE TABLE #assignements (assignmentID INT, cityId INT, desciption VARCHAR(100));
INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (1, 1, 'Test Alpha Metropol'),
       (1, 2, 'Test Alpha Town'),
       (1, 3, 'Test Alpha Village'),
       (2, 1, 'Test Beta Metropol');

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (2, 3, 'Test Beta Village');

Eu preciso de uma consulta, que me retorne todos os IDs de atribuição associados a #cidades (fácil :-)), mas liste apenas as cidades que são usadas em TODOS os IDs de atribuição.

No exemplo acima, devo retornar após o primeiro INSERTapenas

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'

já que a cidade e a vila são usadas apenas uma vez (mas existem dois IDs de atribuição diferentes)

Após o segundo INSERTdeve retornar

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'
1             'Village C'
2             'Village C'

porque o Village C agora também está atribuído a ambos os assignIDs.

Quando eu faria

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (3, 2, 'Test Gamma Town');

a consulta não deve retornar nenhuma linha, porque não há cidade, que é atribuída a todos os três IDs de atribuição (Alfa, Beta e Gama).

É claro que este exemplo é simplificado, na realidade pode haver qualquer número de cidades atribuídas a (atualmente) até 11 IDs de atribuição

sql-server t-sql

1 respostas

  1. Best Answer

    Você pode tentar algo como:

    select a.assignmentID, c.city
from #cities c
join #assignements a on a.cityId = c.cityId
where c.cityId in ( select c.cityId
                    from #cities c
                    left join #assignements a on a.cityId = c.cityId
                    group by c.cityId
                    having count(*) = (select count(distinct assignmentID) 
                                       from #assignements)
                  )
    • 2

relate perguntas