MySQL中是否有类似IN的东西，但是使用AND而不是OR? [英] Is there something in MySQL like IN but which uses AND instead of OR?

问题描述

我需要一条SQL语句来检索键(或任何列)在关联表中的记录，例如:

I need a SQL statement to retrieve records where it key (or any column) is in a associate table, for example:

documentId termId
4             1
4             2
3             3
5             1

此:

SELECT documentId 
  FROM table 
 WHERE termId IN (1,2,3)

...将检索termid值为1 或 2 或 3的任何documentid值.

...will retrieve any documentid value where the termid value is 1 or 2 or 3.

是否有类似的东西，但是返回documentid值，其中termid值是1 和 2 和 3?像IN，但带有AND.

Is there something like this but return documentid values where the termid values are 1 and 2 and 3? Like an IN but with AND.

推荐答案

没有直接的功能，但是有两个选项:

There's no straight forward functionality, but there are two options:

  SELECT t.documentid
    FROM TABLE t
   WHERE t.termid IN (1,2,3)
GROUP BY t.documentid
  HAVING COUNT(DISINCT t.termid) = 3

需要注意的是，您必须使用HAVING COUNT(DISTINCT，因为对于同一documentid，termid的重复项为2将会是误报.并且COUNT必须等于IN子句中的termid值的数量.

The caveat is that you have to use HAVING COUNT(DISTINCT because duplicates of termid being 2 for the same documentid would be a false positive. And the COUNT has to equal the number of termid values in the IN clause.

SELECT t.documentid
  FROM TABLE t
  JOIN TABLE x ON x.termid = t.termid
              AND x.termid = 1
  JOIN TABLE y ON y.termid = t.termid
              AND y.termid = 2
  JOIN TABLE z ON z.termid = t.termid
              AND z.termid = 3

但是，这对于处理变化很大的标准可能会很痛苦.

But this one can be a pain for handling criteria that changes a lot.

这篇关于MySQL中是否有类似IN的东西，但是使用AND而不是OR?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！