当前位置：文江博客话题详情

如何按常见标签数量排序列出相关博客文章？

发布于 2024-07-18 23:20:13 字数 191 浏览 2 评论 0原文

我想显示相关博客文章的列表，并且希望该列表按当前帖子所具有的常见标签的数量排序。每个帖子可以有多个与其关联的标签。这是我的表结构：

[Posts] <-- [posts-to-tags-joining-table] --> [标签]

我正在使用 PHP 和 MySQL - 我可以在一个查询中执行此操作吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嘴硬脾气大 2024-07-25 23:20:13

当然，您可以在一个查询中完成此操作：

SELECT postid, count(tagid) as common_tag_count
FROM posts_to_tags
WHERE tagid IN (SELECT tagid FROM posts_to_tags WHERE postid = 2)
GROUP BY postid ORDER BY common_tag_count DESC;

Sure you can do it in one query:

SELECT postid, count(tagid) as common_tag_count
FROM posts_to_tags
WHERE tagid IN (SELECT tagid FROM posts_to_tags WHERE postid = 2)
GROUP BY postid ORDER BY common_tag_count DESC;

回复收藏 0 原文

眼泪淡了忧伤 2024-07-25 23:20:13

怎么样...：

SELECT COUNT(*) AS numcommon, posts.pid, posts.post FROM posts
               INNER JOIN p2t ON p2t.pid = posts.pid
               WHERE p2t.tid IN
               (SELECT p2t.tid FROM p2t
               INNER JOIN posts ON p2t.pid = posts.pid
               WHERE posts.pid = 1)
               AND posts.pid != 1
               GROUP BY posts.pid
               ORDER BY numcommon

假设pid作为posts表中的主键，tid作为tags表中的主键，p2t（post to tag）表中的两个外键？

What about...:

SELECT COUNT(*) AS numcommon, posts.pid, posts.post FROM posts
               INNER JOIN p2t ON p2t.pid = posts.pid
               WHERE p2t.tid IN
               (SELECT p2t.tid FROM p2t
               INNER JOIN posts ON p2t.pid = posts.pid
               WHERE posts.pid = 1)
               AND posts.pid != 1
               GROUP BY posts.pid
               ORDER BY numcommon

assuming pid as the primary key in the posts table, tid as the primary key in the tags table, both foreign keys in the p2t (post to tag) table?

回复收藏 0 原文

~没有更多了~