主页 / dba / 问题 / 45512
Accepted
user658182
Asked: 2013-07-02 04:02:24 +0800 CST

如何从单列必须包含两个（或更多）值的表中选择项目？

  • 772

我有一个 MySQL 数据库表，它引用了不同的单词及其在文档中的位置。我想返回包含所有单词的文档的 ID。

这是一个示例表。

docid     wordid
1         4
2         4
1         2
1         5

好的，现在假设有人在数据库中查询了 WORDID 为 4、2 和 5 的单词。

我错误的 SQL SELECT 语句类似于：

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

这给了我 0 个结果。

我在其他建议该条款的地方看到过：where in

如果我理解正确，这是编写 OR 子句的另一种方式。我试过这个：

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

但是，这给了我所有的结果。它应该排除 docid 2，因为它不包含其他单词。我期待得到 docid 1。

但是，我可能会where in错误地使用该子句，因为我的数据库经验很少。

如何返回包含所有单词的 docid？

另请注意，我的 where 子句将在 FOR 循环中动态生成。查询可以是一两个词，也可以是 10 或 12 个词。我正在寻找一种考虑速度的查询结构。如果您需要更多信息，请告诉我。

作为参考，我正在尝试将此代码转换为 PHP / MYSQL，但我不理解此处的 sql 语句或其在 MYSQL 中的等效项：

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

mysql select

1 个回答

  1. Best Answer

    这是关系划分问题，在 SO 有一个关于它的问题，有很多方法可以编写这个查询，加上 PostgreSQL 的性能分析：How to filter SQL results in a has-many-through relationship

    在那里无耻地复制代码表格并删除/更改具有 MySQL 缺少的功能的答案的代码，如 CTE、、、EXCEPT等INTERSECT，这里有一些方法可以做到这一点。

    假设：

    • 该表被称为factors
    • 有一个UNIQUE约束(wordid, docid)
    • 有一个documents和一个words表：

    易写，中等效率：

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

    易写，中等效率：

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

    写起来更复杂，在 Postgres 中效率非常好——在 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);

    写起来更复杂，在 Postgres 中效率非常好 - 在 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) ;

    写起来更复杂，在 Postgres 中效率非常好 - 在 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 ;

    易于编写和扩展到任意集合，words但不如JOINandEXISTS解决方案高效：

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

    容易写，效率不高：

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

    享受测试它们:)

    • 9

相关问题