mysql查询所有列是否包含1,检查一列是否包含另一列的所有值-MySQL

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

And another table T2 with just one column of stuffIDs

Table: T2

stuffID

1

2

3

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

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

解决方案

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

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)

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)

and put it all together:

SELECT personID

FROM T1 WHERE stuffID IN (SELECT stuffID FROM t2)

GROUP BY personID

HAVING COUNT(DISTINCT stuffID) = (SELECT COUNT(stuffID) FROM t2)

HTH.