MYSQL，按喜欢排序

发布于 2025-01-06 17:50:33 字数 260 浏览 0 评论 0原文

我有 2 个表“主题”和“帖子”，我想搜索我的帖子并按最匹配的主题排序。

现在我有这个：

SELECT Threads.* 
FROM Posts 
INNER JOIN Threads ON Posts.ThreadID=Threads.ThreadID 
WHERE Posts.Content LIKE '%" . $search .  "%' 
     OR Posts.User LIKE '%" . $search ."%'

原文

I have 2 tables Thread and Posts, i would like to search my posts and order by thread with most matches.

Right now i have this:

SELECT Threads.* 
FROM Posts 
INNER JOIN Threads ON Posts.ThreadID=Threads.ThreadID 
WHERE Posts.Content LIKE '%" . $search .  "%' 
     OR Posts.User LIKE '%" . $search ."%'

分享到QQ

分享到微博

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

发布评论

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

遗心遗梦遗幸福 2025-01-13 17:50:33

您可以添加一个联接来计算每个线程的帖子数。由于您没有从帖子中选择任何列，因此这是您需要的唯一联接。

select  t.* 
from    Threads t
join    (
        select  ThreadID
        ,       count(*) as PostCount
        from    Posts
        where   p.Content LIKE '%" . $search .  "%' 
                or p.User LIKE '%" . $search ."%'
        group by
                ThreadID
        ) PostCount
on      PostCount.ThreadID = t.ThreadID
order by
        PostCount.PostCount desc

You could add a join to calculate the number of posts per thread. Since you're not selecting any columns from Posts, that's the only join you'll need.

select  t.* 
from    Threads t
join    (
        select  ThreadID
        ,       count(*) as PostCount
        from    Posts
        where   p.Content LIKE '%" . $search .  "%' 
                or p.User LIKE '%" . $search ."%'
        group by
                ThreadID
        ) PostCount
on      PostCount.ThreadID = t.ThreadID
order by
        PostCount.PostCount desc

回复收藏 0 原文

~没有更多了~