在MySQL中选择多对多关系 [英] Select in a many-to-many relationship in MySQL

问题描述

我在MySQL数据库中有两个表Locations和Tag，还有第三个表LocationsTagsAssoc，该表将两个表关联起来并将它们视为多对多关系.

I have two tables in a MySQL database, Locations and Tags, and a third table LocationsTagsAssoc which associates the two tables and treats them as a many-to-many relationship.

表结构如下:

Locations
---------
ID int (Primary Key)
Name varchar(128)

LocationsTagsAssoc
------------------
ID int (Primary Key)
LocationID int (Foreign Key)
TagID int (Foreign Key)

Tags
----
ID int (Primary Key)
Name varchar(128)

因此，每个位置都可以用多个标记词标记，并且每个标记词都可以标记到多个位置.

So each location can be tagged with multiple tagwords, and each tagword can be tagged to multiple locations.

我想做的只是选择带有 提供的所有标签名称 的位置.例如:

What I want to do is select only Locations which are tagged with all of the tag names supplied. For example:

我希望所有标记有树"和秋千"的位置.应该选择位置公园"，而不应该选择位置森林".

I want all locations which are tagged with both "trees" and "swings". Location "Park" should be selected, but location "Forest" should not.

任何见识将不胜感激.谢谢！

Any insight would be appreciated. Thanks!

推荐答案

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

There are two ways to do this. I prefer the first way, which is to self-join for each tag:

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往往会产生一个临时表，并且性能很慢:

The other way also works, but using GROUP BY in MySQL tends to incur a temporary table and performance is slow:

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;

---

@Erikoenig的评论:

---

Re comment from @Erikoenig:

如果要确保没有多余的标签，可以按照以下方式进行操作:

If you want to make sure there are no extra tags, you can do it this way:

SELECT l.*
FROM Locations l
JOIN LocationsTagsAssoc a ON a.LocationID = l.ID
JOIN Tags t ON a.TagID = t.ID
GROUP BY l.ID
HAVING COUNT(*) = 3 AND SUM(t.Name IN (?, ?, ?)) = 3;

取出WHERE子句允许对其他标记进行计数(如果有的话).因此COUNT()可能大于3.

Taking out the WHERE clause allows other tags to be counted, if there are any. So the COUNT() may be greater than 3.

或者如果计数恰好是三个标签，但是这三个标签中的一些不是正确的标签，则HAVING子句中的SUM()条件可确保所需的所有三个标签都出现在组中.

Or if the count is exactly three tags, but some of these three are not the correct tags, then the SUM() condition in the HAVING clause makes sure that all three tags you want are present in the group.

这篇关于在MySQL中选择多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！