mysql - MySQL-一种高效的方法来选择与多对多属性列表共存的所有属性

给出下表：

Thing
  idThing
Attribute
  idAttribute
ThingHasAttribute
  idThing
  idAttribute

我想选择与给定属性列表共同出现的所有属性。

我的意思是：

Assume there are 2 Things in table Thing:
Thing 1 has Attributes 1, 2, 5
Thing 2 has Attributes 1, 2, 8, 9
Given the Attribute 1, the co-occuring attributes are 2, 5, 8, 9
Given the Attributes 1, 2, the co-occuring attributes are 5, 8, 9
Given the Attributes 1, 2, 8, the co-occuring attribute is 9

我设法反复尝试一个解决方案，但在我看来，肯定有一些更简单的方法。你们中有人知道选择一种更简单或更重要的更快的方法吗？

我发现的是：

SELECT GROUP_CONCAT(DISTINCT tag1.idAttribute SEPARATOR ',') FROM (
 SELECT DISTINCT Thing.idThing FROM Thing
 JOIN ThingHasAttribute ta1 ON Thing.idThing=ta1.idThing AND ta1.idAttribute = 1
 JOIN ThingHasAttribute ta2 ON Thing.idThing=ta2.idThing AND ta2.idAttribute = 2
 ...
 ) thingWithAttrs
JOIN ThingHasAttribute tag1 ON thingWithAttrs.idThing = tag1.idThing
WHERE tag1.idAttribute NOT IN ('1', '2', ...)

最佳答案

如果我了解“同现”的含义，则应尝试将INNER JOIN与关联表一起使用，这将为您提供两个/所有表中的记录。

SELECT *
FROM ThingHasAttribute THA
    INNER JOIN Thing T
        ON T.idThing = THA.idThing
    INNER JOIN Attribute A
        ON A.idAttribute = THA.idAttribute