“相似帖子”像使用MS SQL Server的功能？ [英] "Similar Posts" like functionality using MS SQL Server?

问题描述

我有很多文章存储在MS SQL server 2005数据库中的表 - 名为文章 -

I have lots of article store in MS SQL server 2005 database in a table called Articles-

"Articles (ArticleID, ArticleTitle, ArticleContent)"

现在我想要一些SP或SQL查询，用户的输入（非常类似于类似的帖子在博客或相关问题在stackoverflow）。匹配应该同时适用于ArticleTitle和ArticleContent。

Now I want some SP or SQL query which could return me similar Article against any user's input (very much like "Similar Posts" in blogs OR "Related Questions" in stackoverflow). The matching should work on both ArticleTitle and ArticleContent. The query should be intelligent enough to sort the result on the basis on their relevancy.

是否可以在MS SQL Server 2005中执行此操作？

Is it possible to do this in MS SQL Server 2005?

推荐答案

这样的工作，可能是一种排序系统。您可能必须在应用程序中分割字符串以构建SQL字符串，但是我已经使用类似的方法构建有效的网站搜索。

Something like this might work, a kind of ranking system. You would probably have to split the string in your application to build a SQL string, but I have used similar to build an effective site search.

Select
Top 10
ArticleID,
ArticleTitle,
ArticleContent
From
Articles
Order By
(Case When ArticleTitle = 'Article Title' Then 1 Else 0 End) Desc,
(Case When ArticleTitle = 'Article' Then 1 Else 0 End) Desc,
(Case When ArticleTitle = 'Title' Then 1 Else 0 End) Desc,
(Case When Soundex('Article Title') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,
(Case When Soundex('Article') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,
(Case When Soundex('Title') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%Title%', ArticleTitle) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%', ArticleTitle) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Title%', ArticleTitle) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%Title%', ArticleContent) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%', ArticleContent) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Title%', ArticleContent) > 0 Then 1 Else 0 End) Desc

来自order by子句的语句，以根据您的数据改进列表。

You can then add/remove case statements from the order by clause to improve the list based on your data.

这篇关于“相似帖子”像使用MS SQL Server的功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！