MySQL单表，按多行选择值

Question

从下表中，我如何选择具有特定attributeIds组合的所有animalIds，例如，如果我提供了attributeIds 455和685，我希望返回animalIds 55和93

表名: animalAttributes

id      attributeId     animalId
1       455             55
2       233             55
3       685             55
4       999             89
5       455             89
6       333             93
7       685             93
8       455             93

我有以下查询，似乎可以工作，但是，我不确定是否有更健壮的方法？

  SELECT animalId
    FROM animalAttributes
   WHERE attributeId IN (455,685)
GROUP BY animalId 
  HAVING COUNT(DISTINCT attributeId) = 2;

Answer 1

SELECT DISTINCT animalId
FROM animalAttributes
WHERE attributeId IN (455,685)

或

SELECT animalId
FROM animalAttributes
WHERE attributeId IN (455,685)
GROUP BY animalId

Answer 2

如果你真的想要准确的结果，你可以使用这样一个简单的方法：

select distinct base.animalId
from animalAttributes base
join animalAttributes a on base.animalId = a.animalId
     and a.attributeId = 455
where base.attributeId = 685

如果您以后需要3个匹配的属性，您只需添加另一个连接：

select distinct base.animalId
from animalAttributes base
join animalAttributes a on base.animalId = a.animalId
     and a.attributeId = 455
join animalAttributes b on base.animalId = b.animalId
     and b.attributeId = 999
where base.attributeId = 685

Answer 3

SELECT DISTINCT `animalId` FROM `animalAttributes` WHERE `attributeId` = 455
INTERSECT
SELECT DISTINCT `animalId` FROM `animalAttributes` WHERE `attributeId` = 685