检查一列是否包含另一列的所有值-MySQL

问题描述：

假设我有一个包含人员ID和其他东西ID的表T1，如下所示:

Let's suppose I have a table T1 with people IDs and other stuff IDs, as the following

Table: T1
personID | stuffID 
    1    |    1
    1    |    2
    1    |    3
    1    |    4
    2    |    1
    2    |    4
    3    |    1
    3    |    2

还有另一个表T2，其中只有一列stuffIDs

And another table T2 with just one column of stuffIDs

Table: T2
stuffID
   1  
   2  
   3

我将通过SELECT得到的结果是一个与T2的所有stuffID相关联的peopleID表.

The result that I would get, by a SELECT, is a table of peopleIDs who are connected with ALL the stuffIDs of T2.

在此示例之后，结果将仅为id 1(即使关联的所有stuffID都包含在T2.stuffID中，personID 3也不会出现).

Following the example the result would be only the id 1 (the personID 3 has no to appear even if all the stuffIDs it is associated are included in T2.stuffID).

答

如果我正确理解，您希望从T1中检索所有在T2中找到所有关联的stuffID的personID.

If I understand correctly, you want to retrieve all the personID's from T1 that have all associated stuffID's found in T2.

您可以按以下步骤将其分解: 首先，找到所有与嵌套查询匹配的T1条目

You can break this up as follows: First of all, find all the T1 entries that match with a nested query

SELECT personID 
FROM T1 WHERE stuffID IN (SELECT stuffID FROM t2)

现在，您需要检查该集合中的哪些条目包含所需的所有stuffID.

Now you need to check which of the entries in this set contains ALL the stuffID's you want

GROUP BY personID
HAVING COUNT(DISTINCT stuffID) = (SELECT COUNT(stuffID) FROM t2)

将它们放在一起:

SELECT personID 
FROM T1 WHERE stuffID IN (SELECT stuffID FROM t2)
GROUP BY personID
HAVING COUNT(DISTINCT stuffID) = (SELECT COUNT(stuffID) FROM t2)

HTH.

检查一列是否包含另一列的所有值-MySQL

相关推荐