Início / dba / Perguntas / 133271
Accepted
user3202098
Asked: 2016-03-25 03:59:49 +0800 CST

SQL obtendo IDs de uma tabela com várias entradas em outra tabela

  • 772

Em meu banco de dados tenho duas tabelas: Persons e PersonAttributes.
Uma Pessoa pode ter vários Atributos associados a ela.

O que eu gostaria de fazer é obter uma lista de todas as Persons(/IDs) que possuem pelo menos os atributos especificados associados a elas na tabela PersonAttributes.

Para um atributo, isso pode ser feito facilmente com apenas uma instrução WHERE. Meu problema é que quero fazer isso para vários atributos.

A única solução que encontrei é fazer um SELECT para cada atributo e, em seguida, juntá-los. Embora eu possa criar essa consulta programaticamente, ela parece bastante complexa e esperava que houvesse uma solução mais fácil.

Para melhor entendimento aqui está um SQLFiddle incluindo minha solução para 3 atributos.

EDIT: Alterado o link SQLFiddle. A consulta agora se parece com isso:

SELECT Persons.ID 
FROM Persons
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'b') t1
    ON Persons.ID = t1.ID
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'c') t2
    ON t1.ID = t2.ID
  JOIN (SELECT * FROM PersonAttributes WHERE PersonAttributes.Attr = 'd') t3
    ON t2.ID = t3.ID;
performance query-performance

1 respostas

  1. Best Answer

    Aparentemente, sua PersonAttributesmesa foi projetada usando o modelo EAV . Esse modelo tem a vantagem de ser facilmente extensível: os atributos são armazenados como linhas e é fácil adicionar novas linhas. Entretanto, a consulta a esse tipo de tabela é mais difícil do que aquelas elaboradas de forma tradicional (atributos armazenados em colunas).

    Sua solução é bastante ilustrativa de quanto mais problemas pode ser realizar uma tarefa bastante simples com uma tabela modelada por EAV. Na verdade, é uma das maneiras comuns de resolver um problema como o seu, embora eu sugira que você tente reescrevê-lo sem usar tabelas derivadas – assim:

    SELECT p.ID 
FROM Persons AS p
  JOIN PersonAttributes AS paB ON p.ID = paB.ID
  JOIN PersonAttributes AS paC ON p.ID = paC.ID
  JOIN PersonAttributes AS paD ON p.ID = paD.ID
WHERE paB.Attr = 'b'
  AND paC.Attr = 'c'
  AND paD.Attr = 'd';

    O desempenho provavelmente permanecerá o mesmo da sua sintaxe, mas sem tornar a consulta mais rápida, essa reescrita pelo menos a tornará mais concisa e sem dúvida mais legível.

    Dito isto, existe outro método, bastante comum também, que você pode empregar, que pode oferecer melhor desempenho à medida que o número de atributos aumenta. Ele usa agrupamento e agregação:

    SELECT
  ID
FROM
  PersonAttributes
WHERE
  Attr IN ('b', 'c', 'd')
GROUP BY
  ID
HAVING
  COUNT(*) = 3
;

    Por esse método, todas as linhas que possuem qualquer um dos atributos especificados são recuperadas e agrupadas por ID. Para determinar os grupos (pessoas) com todos os três atributos, um filtro HAVING é introduzido para comparar o número de linhas * em cada grupo com o número total de atributos na INlista.

    O método pode ser ligeiramente generalizado se você puder armazenar os atributos a serem pesquisados ​​em uma tabela (temporária). Veja como ficaria nesse caso:

    SELECT
  pa.ID
FROM
  PersonAttributes AS pa
  INNER JOIN QueriedAttributes AS qa ON pa.Attr = qa.Attr
GROUP BY
  pa.ID
HAVING
  COUNT(*) = (SELECT COUNT(*) FROM QueriedAttributes)
;

    Nenhuma cláusula WHERE aqui – ela é substituída pela junção à tabela de atributos consultados, e o número total de atributos necessários para correspondência é derivado da mesma tabela em vez de ser codificado.

    Esse tipo de problema é comumente conhecido como divisão relacional . É discutido em detalhes neste artigo de Joe Celko:

    * Esta implementação particular do método de agrupamento assume que sempre há uma linha por atributo por pessoa, então COUNT(*)funciona corretamente. Se atributos do mesmo tipo puderem ser repetidos por pessoa, ou posteriormente serão permitidos, use COUNT(DISTINCT Attr)em vez disso.

    • 3

relate perguntas