mysql and or 代替 in_MySQL中是否有类似IN的东西，但是使用AND而不是OR？

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

This:

SELECT documentId

FROM table

WHERE termId IN (1,2,3)

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

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:

Using GROUP BY/HAVING

SELECT t.documentid

FROM TABLE t

WHERE t.termid IN (1,2,3)

GROUP BY t.documentid

HAVING COUNT(DISINCT t.termid) = 3

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.

Using JOINs

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.