Início / dba / Perguntas / 45512
Accepted
user658182
Asked: 2013-07-02 04:02:24 +0800 CST

Como seleciono itens de uma tabela em que uma única coluna deve conter dois (ou mais) valores?

  • 772

Eu tenho uma tabela de banco de dados MySQL que faz referência a diferentes palavras e suas localizações em documentos. Desejo retornar os IDs dos documentos que contêm todas as palavras.

Aqui está uma tabela de exemplo.

docid     wordid
1         4
2         4
1         2
1         5

Ok, agora diga que alguém consultou o banco de dados para as palavras que tinham WORDIDs 4, 2 e 5.

Minha instrução SQL SELECT errônea seria algo como:

Select docid from table where wordid = 4 and wordid = 2 and wordid = 5

Isso está me dando 0 resultados.

Eu vi em outros lugares onde a where incláusula foi sugerida:

Se bem entendi, esta é outra maneira de escrever uma cláusula OR. Eu tentei isso:

select docid from table where wordid in (4,2,5)

Mas, isso está me dando todos os resultados. Deve excluir docid 2, pois não contém as outras palavras. Estou esperando apenas obter docid 1.

No entanto, eu poderia estar usando a where incláusula incorretamente, pois tenho muito pouca experiência em banco de dados.

Como posso retornar docids que contêm todas as palavras?

Observe também que minha cláusula where será gerada dinamicamente em um loop FOR. A consulta pode ser tão simples quanto uma ou duas palavras, ou pode ter 10 ou 12 palavras. Estou procurando uma estrutura de consulta que leve em consideração a velocidade. Por favor, deixe-me saber se você precisar de mais informações.

Para referência, estou tentando converter este código em PHP/MYSQL, mas não entendo a instrução sql aqui ou seu equivalente em MYSQL:

http://my.safaribooksonline.com/book/web-development/9780596529321/4dot-searching-and-ranking/querying

mysql select

1 respostas

  1. Best Answer

    Este é o problema da divisão relacional e há uma pergunta sobre isso no SO, com várias maneiras de escrever essa consulta, além de análise de desempenho para o PostgreSQL: Como filtrar resultados de SQL em uma relação tem-muitos-através

    Copiando descaradamente o formulário de código e removendo/alterando o código para respostas que não possuem recursos do MySQL, como CTEs, EXCEPT, INTERSECTetc., aqui estão algumas maneiras de fazer isso.

    Suposições:

    • a mesa chama-sefactors
    • existe uma UNIQUErestrição(wordid, docid)
    • há um documentse uma wordstabela:

    Fácil de escrever, eficiência média:

    -- Query 1 -- by Martin
SELECT d.docid, d.docname
FROM   document d
JOIN   factors f USING (docid)
WHERE  f.wordid IN (2, 4, 5)
GROUP  BY d.docid
HAVING COUNT(*) = 3 ;           -- number of words

    Fácil de escrever, eficiência média:

    -- Query 2 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
JOIN   (
   SELECT docid
   FROM   factors
   WHERE  wordid IN (2, 4, 5)
   GROUP  BY docid
   HAVING COUNT(*) = 3
   ) f USING (docid) ;

    Mais complexo de escrever, eficiência muito boa no Postgres - provavelmente ruim no MySQL:

    -- Query 4 -- by Derek
SELECT d.docid, d.docname
FROM   documents d
WHERE  d.docid IN (SELECT docid FROM factors WHERE wordid = 2)
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 4);
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 5);

    Mais complexo de escrever, eficiência muito boa no Postgres - e provavelmente o mesmo no MySQL:

    -- Query 5 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
WHERE  EXISTS (SELECT * FROM factors 
               WHERE  docid = d.docid AND wordid = 2)
AND    EXISTS (SELECT * FROM factors 
               WHERE  docid = d.docid AND wordid = 4)
AND    EXISTS (SELECT * FROM factors 
               WHERE  docid = d.docid AND wordid = 5) ;

    Mais complexo de escrever, eficiência muito boa no Postgres - e provavelmente o mesmo no MySQL:

    -- Query 6 -- by Sean
SELECT d.docid, d.docname
FROM   documents d
JOIN   factors x ON d.docid = x.docid
JOIN   factors y ON d.docid = y.docid
JOIN   factors z ON d.docid = z.docid
WHERE  x.wordid = 2
AND    y.wordid = 4
AND    z.wordid = 5 ;

    Fácil de escrever e estender para um conjunto arbitrário, wordsmas não tão eficiente quanto as soluções JOINe EXISTS:

    -- Query 7 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM   words AS w 
   WHERE  w.wordid IN (2, 4, 5)
   AND    NOT EXISTS (
      SELECT *
      FROM   factors AS f 
      WHERE  f.docid = d.docid 
      AND    f.wordid = w.wordid 
      )
   );

    Fácil de escrever, não é boa eficiência:

    -- Query 8 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 2 AS wordid UNION  ALL
      SELECT 4 UNION ALL
      SELECT 5
      ) AS w
   WHERE NOT EXISTS (
      SELECT *
      FROM   factors AS f 
      WHERE  f.docid = d.docid 
      AND    f.wordid = w.wordid 
      )
   );

    Aproveite para testá-los :)

    • 9

relate perguntas