mysql - 使用 MySQL 查询仅获取多对多关系中可用的标签

Question

我有三个具有多对多关系的表； 媒体、关系和标签

      Media Table                      Relations Table                   Tags Table

| media-id | media-name |       | rel-id | media-id | tag-id |       | tag-id | tag-name|
-------------------------       ------------------------------       --------------------
| 1        | "Media 1"  |       | 1      | 1        | 1      |       | 1      | "tag 1" |
| 2        | "Media 2"  |       | 2      | 2        | 2      |       | 2      | "tag 2" |
                                | 3      | 1        | 3      |       | 3      | "tag 3" |
                                | 4      | 2        | 3      |       | 4      | "tag 4" |
                                | 5      | 1        | 4      |

我需要一个 MySQL 查询，当给出一个或多个标签时，它将返回所有其他可能的标签组合。这里有几个例子。

示例 1

给定: 标签 1

返回: 标签 1、标签 3 和 标签 4

因为: 标签 1 正在标记 Media 1，因此它应该返回其所有标签(标签 1 、标签 3 和 标签 4)

示例 2

给定: 标签 3

返回: 标签 1、标签 2、标签 3 和 标签 4返回

因为: 标签 3 正在标记 Media 1 和 Media 2，因此返回它们的所有标签(标签 1、标签 2、标签 3、标签 4)

示例 3

给定: 标签 2 和 标签 3

返回: 标签 2 和 标签 3

因为: tag 3 标记两者 Media 1 和 Media 2，但只有 Media 2 具有 tag 2，因此仅返回它的标签，并排除 Media 1 的标签

我已经搜索类似的内容有一段时间了，但我没有找到任何与弧形答案相关的内容。我能得到的最接近的是这里的查询:

SELECT `tags`.`tag-name`
FROM `tags`
WHERE `tags`.`tag-id` IN (
    SELECT `relations`.`tag-id`
    FROM `relations`
    WHERE `relations`.`media-id` IN (
        SELECT `relations`.`media-id`
        FROM `relations`
        WHERE `relations`.`tag-id` IN (
            SELECT `tags`.`id`
            FROM `tags`
            WHERE `tags`.`tag-name` IN ('tag 1')
        )
    )
)

这很有效，也许不是最有效的，但我可以理解。

唯一的问题是，如果我在示例 3 上运行它，它将返回所有标签，而不是从 Media 1 中排除标签。

有没有办法可以改变我的 MySQL 查询来做到这一点？

Answer 1

问题的一般解决方案涉及表和聚合之间的多个联接，如下所示:

select t2.tag_name
from tags t
inner join relations r on r.tag_id = t.tag_id 
inner join media m on m.media_id = r.media_id 
inner join relations r2 on r2.media_id = m.media_id
inner join tags t2 on t2.tag_id = r2.tag_id
where t.tag_name in ('tag 2', 'tag 3')
group by t2.tag_name
having count(distinct t.tag_id) = 2

这个:

('tag 2', 'tag 3')

是您希望查询获取结果的标签列表。
以及HAVING子句中条件右侧的数字:

count(distinct t.tag_id) = 2

是该列表中的项目数。
请参阅demo .
如果只有 1 个查询标签，代码可以简化为:

select t2.tag_name
from tags t
inner join relations r on r.tag_id = t.tag_id 
inner join media m on m.media_id = r.media_id 
inner join relations r2 on r2.media_id = m.media_id
inner join tags t2 on t2.tag_id = r2.tag_id
where t.tag_name = 'tag 1'
group by t2.tag_name