在mysql中查找相关主题? [英] Find related topics in mysql?
本文介绍了在mysql中查找相关主题?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有如下数据库:
Post {id, title}
Topic {id, name}
Post_Topic {PostId, TopicId}
由于每个帖子可能有很多主题,所以我想根据它们一起出现在帖子中的次数来获取相关主题.例如:
Since each post may have many topics, so I want to get related topics based on the number of times they appears in posts together. For example:
post 1 has topics {database, mysql, mobile}
post 2 has topics {database, mysql, android}
post 3 has topics {database, mysql, algorithm}
post 4 has topics {database, algorithm, web programming}
根据上面的数据,如果输入的是database
,那么相关主题应该是按顺序显示的:
Based on the above data, if the input is database
, then related topics should be displayed in order:
mysql (appears 3 times with database)
algorithm (appears 2 times with database)
android
mobile
我该如何编写 sql 来实现这一点?
How can I write the sql to achieve that?
推荐答案
您可能会找到更好的方法(有两次条件不是很好),但是,使用 join 和 exists 子句,您将得到您想要的想要.
You may find a better way (not great to have a condition two times), but, with a join and an exists clause, you'll get what you want.
select t_id, t.title, count(*) as cnt
from post_topic pt
join topic t on t.id = pt.t_id
where exists (select null
from post_topic pt1
join topic t1 on pt1.t_id = t1.id
where t1.title = 'database' and p_id = pt.p_id)
and t.title <> 'database'
group by t_id, t.title
order by cnt desc;
这篇关于在mysql中查找相关主题?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文