Início / dba / Perguntas / 28431
Accepted
Jmoney38
Asked: 2012-11-10 06:46:11 +0800 CST

Subseleção muitos-para-um em consulta única

  • 772

Eu tenho duas tabelas com uma chave estrangeira de T1->T2, em um relacionamento um-para-muitos. Ou seja, 1 tupla na tabela T1 está associada a 0..N tuplas em T2.

Para criar um exemplo simples, digamos que T1 é Carros e T2 é uma tabela de imperfeições. Então, um carro pode ter 0..N imperfeições, e armazenamos essas imperfeições em T2 como números inteiros.

Eu gostaria de selecionar * apenas daqueles carros em Carros que contêm imperfeições i1 E i2.

Em vez disso, executar uma OU é muito fácil:

SELECT * FROM cars AS T1 
  WHERE EXISTS (
    SELECT imperfection FROM Imperfections as T2 
      WHERE T1.uid = T2.uid AND (imperfection = 1 OR imperfection = 2)
  );

Tenho tentado definir a lógica usando a interseção, mas, neste ponto, estou me perguntando se estou complicando demais.

database-design postgresql

3 respostas

  1. Best Answer

    Você poderia ter duas cláusulas existentes separadas:

    SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 1
    )
    AND EXISTS
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 2
    );

    Isso significa duas semijunções à esquerda em sua tabela de imperfeições, mas se você tiver um índice em UID e imperfeição, essas devem ser triviais.

    Há duas vantagens em usar WHERE EXISTS sobre JOIN neste caso. A primeira é que um semi-join esquerdo para após a primeira correspondência, portanto, pode ser mais rápido que um join (que precisa passar por todos os registros). A segunda é que, se de alguma forma você não tiver boas restrições de dados e tiver dois registros em Imperfeições com o mesmo UID e Imperfeição, poderá acabar com linhas duplicadas em sua consulta principal.

    • 7

  2. O caminho mais curto e mais fácil de percorrer é

    SELECT c.* 
FROM 
    cars AS c
    JOIN Imperfections as i1 ON c.uid = i1.uid AND i1.imperfection = 1
    JOIN Imperfections as i2 ON c.uid = i2.uid AND i2.imperfection = 2
;

    O (INNER) JOINsteste para a própria existência.

    Isso vale se (uid, imperfection)for UNIQUE, o que seria plausível aqui, mas não foi definido. Pode haver subtipos de imperfeições ... então você precisa de um DISTINCTou GROUP BY. USING (uid)seria ainda mais curto do que ON c.uid = i1.uid. As imperfeições entrariam em uma WHEREcláusula então.

    A próxima ideia seria a mesma que Kevin descreve em sua resposta, então não a incluirei aqui.

    • 5 
  3. SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT 1 FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection IN(1,2)
        HAVING COUNT(DISTINCT imperfection) =2
     )
    • 3

relate perguntas