MySQL查询按标签查找相关帖子并按流行排序？

Question

这是我之前读过的一个问题:

这是一个复杂的情况(对我来说)，我希望有人能 这里可以帮助我。我已经做了很多寻找解决方案的工作 并且找不到一个。这基本上是我的 情况...(我已将其缩减，因为如果有人可以帮助我 创建这个查询，我可以从那里获取它。)

TABLE articles (article_id, article_title)

TABLE articles_tags (row_id, article_id, tag_id)

TABLE article_categories (row_id, article_id, category_id)

All of the tables have article_id in common. I know what all of the tag_id and category_id rows are. What I want to do is return a list of all the articles that article_tags and article_categories MAY have in common, ordered by the number of common entries.

例如:

article1 - tags: tag1, tag2, tag3 - categories: cat1, cat2

article2 - tags: tag2 - categories: cat1, cat2

article3 - tags: tag1, tag3 - categories: cat1

So if my article had "tag1" and "cat1 and cat2" it should return the articles in this order:

article1 (tag1, cat1 and cat2 in common)

article3 (tag1, cat1 in common)

article2 (cat1 in common)

Any help would genuinely be appreciated! Thank you!

根据这个答案:

How to Create MySQL Query to Find Related Posts from Multiple Tables?

我已经使用了查询并且它运行良好:

SELECT article_id,count(*) AS q 
FROM article_tags 
WHERE id_tag IN (
    SELECT id_tag 
    FROM article_tags 
    WHERE article_id=41
) 
AND article_id!=41 
GROUP BY article_id 
ORDER BY q DESC

现在我想按 article_view 对结果进行排序(还有 1 个表)。

TABLE articles_info (article_id, article_view, article_vote, article_something)

Answer 1

试试这个:

SELECT at.article_id,count(*) AS q 
FROM article_tags at
INNER JOIN article_info ai ON at.article_id = ai.article_id
WHERE at.id_tag IN (
    SELECT id_tag 
    FROM article_tags 
    WHERE article_id=41
) 
AND at.article_id!=41 
GROUP BY at.article_id 
ORDER BY q DESC, ai.article_view DESC