模仿array_intersect在MySQL中 [英] Imitate array_intersect in MySQL

问题描述

我有这样一个表格：

Col1    Col2
word1   872
word1   333
word2   631
word3   982
word3   111
word4   111
word5   631
word6   333
word6   453

我想接收多个SELECT查询的中发生的所有结果的结果，但只有那些结果。例如，如果我寻找 SELECT COL2 FROM表WHERE COL1 ='字1 和 SELECT COL2 FROM表WHERE COL1 ='word6，我想这两个语句联合起来，有一个MySQL的声明，将只返回出现在这两个上面的例子中查询的结果。 （同样如在PHP array_intersect作品。）

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

我需要一个单独的查询，将任意数量的COL1词做到这一点。

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.

这篇关于模仿array_intersect在MySQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！