MySQL 根据前一条语句的结果进行选择

Question

我对 SQL 很陌生，我遇到了这个问题:我有关系表，其中人有权力

A has X
A has Z
B has X
B has Y
C has Y
D has Z

我想选择所有具有权力 X 和 Y 的人，在本例中是 B。当然，我会有更复杂的权力组合。

Answer 1

做这样的事情

SELECT person
FROM tableName
WHERE power IN ('x', 'y')
GROUP BY Person
HAVING COUNT(*) = 2

• SQLFiddle Demo Link

如果唯一约束不是对每个人强制执行权力，则需要使用DISTINCT

SELECT person
FROM tableName
WHERE power IN ('x', 'y')
GROUP BY Person
HAVING COUNT(DISTINCT power) = 2

• SQLFiddle Demo Link (with duplicate)