mysql - 怎样才能拥有一切的人呢？ mysql

Question

下表为

person  things
A   TV
A   CAR
A   MOBILE
A   HOUSE
B   TV
C   TV
C   CAR
C   MOBILE
C   HOUSE
D   TV
E   TV
F   TV

在上面我可以看到 A 和 C 拥有所有的东西。

根据书本示例，我找到了每个人都有什么东西？使用下面的查询，

SELECT things
FROM tbl_happiness as x 
WHERE NOT EXISTS 
( 
SELECT person 
FROM tbl_happiness as y 
WHERE NOT EXISTS 
( 
SELECT 1
FROM tbl_happiness as z 
WHERE z.person = y.person 
AND z.things = x.things
 )
);

是否可以使用更改别名的相同查询来查找

哪个人拥有所有的东西？

Answer 1

我使用group by和having来处理这些“set-within-sets”查询:

select person
from table t
group by person
having count(distinct thing) = (select count(distinct thing) from table t);

如果没有重复项，您可以使用:

having count(*) = (select count(distinct thing) from table t);