mysql intersect用法格式,模仿array_intersect在MySQL中

I have a table like this:

Col1 Col2

word1 872

word1 333

word2 631

word3 982

word3 111

word4 111

word5 631

word6 333

word6 453

I would like to receive the results of multiple SELECTs, but only those results which occur in all results. For example, if I am searching for SELECT col2 FROM table WHERE col1='word1' and SELECT col2 FROM table WHERE col1='word6', I want to join these two statements together to have a single MySQL statement which will return only the results that occur in both of the above example queries. (The same as array_intersect works in PHP.)

I need a single query that will do this for any number of col1 words.

Sorry, if I haven't explained very well. It was difficult to explain.

解决方案

The best approach to this problem is to use aggregation:

select col2

from t

group by col2

having max(col1 = 'word1') = 1 and

max(col1 = 'word2') = 1

This easily generalizes to more words.