mysql - MYSQL多对多关系问题

Question

以下是我的表格结构：

posts table:
---------------------
|id|title|body|etc..|
---------------------

tags table:
---------------
|id|name|etc..|
---------------

post_tag table (the pivot table):
-------------------
|id|post_id|tag_id|
-------------------

如您所见，许多帖子可以有许多标签，反之亦然。我要问的是如何根据它们有多少共同标签来查询相关帖子？例如，如果我有一些类似以下的帖子：

1. 这是带有标签的帖子：#sql、#nosql、#database
2. 这是带有标签的两个帖子：#sql、#nosql
3. 这是带有标签的第三个帖子：#database
4. 这是带有标签的第四篇文章：#stackoverflow

如何查询与帖子 1 相关（具有共同标签）的帖子并根据它们共同的标签数量对结果进行排序？我尝试了以下查询：

SELECT p.* 
FROM posts p 
INNER JOIN post_tag pt ON p.id = pt.post_id 
INNER JOIN tags t on pt.tag_id = t.id 
WHERE t.id IN (
    SELECT t.id 
    FROM tags t 
    INNER JOIN post_tag pt ON t.id = pt.tag_id 
    INNER JOIN posts p ON pt.post_id = p.id 
    WHERE p.id = ?
) AND p.id != ? LIMIT 8

它有效，但如果帖子共享多个标签，则会返回重复项。另外，我不知道如何根据它们共有的标签数量对结果进行排序。就是这样，提前感谢！

标签： mysqlsqljoinmariadbmany-to-many

Answer 1

您可以按如下方式加入和聚合：

select p1.id, p2.id, count(*) nb_tags_in_common
from posts p1
inner join post_tag pt1 on pt1.post_id = p1.id
inner join post_tag pt2 on pt2.tag_id = pt1.tag_id
inner join posts p2 on p2.id = pt2.post_id
group by p1.id, p2.id
order by count(*) desc