mysql可选择关系_在MySQL中选择多对多关系

有两种方法可以做到这一点.我更喜欢第一种方式,即为每个标签自我加入：

SELECT l.*

FROM Locations l

JOIN LocationsTagsAssoc a1 ON a1.LocationID = l.ID

JOIN Tags t1 ON a1.TagID = t1.ID AND t1.Name = ?

JOIN LocationsTagsAssoc a2 ON a2.LocationID = l.ID

JOIN Tags t2 ON a2.TagID = t2.ID AND t2.Name = ?

JOIN LocationsTagsAssoc a3 ON a3.LocationID = l.ID

JOIN Tags t3 ON a3.TagID = t3.ID AND t3.Name = ?;

另一种方式也有效,但在MySQL中使用GROUP BY往往会产生临时表并且性能很慢：

SELECT l.*

FROM Locations l

JOIN LocationsTagsAssoc a ON a.LocationID = l.ID

JOIN Tags t ON a.TagID = t.ID

WHERE t.Name IN (?, ?, ?)

GROUP BY l.ID

HAVING COUNT(*) = 3;