sql - T-SQL存储过程返回google风格 "suggested"搜索结果

Question

好的，使用 SQL Server 2008。在我的网页上，我有一个连接了 jQuery-UI AutoComplete 的文本框。

现在我需要一个存储过程来搜索单个表(或我认为的多个连接表)的所有列，以查找来自文本框/自动完成 AJAX 调用的搜索字符串，并返回“建议的”搜索字符串。我正在使用 AdventureWorks 数据库进行测试(产品表)

例如，产品表包含产品名称和产品编号(以及其他)列，我想根据用户输入返回建议的搜索字符串，用户可以在其中输入产品名称和/或产品编号。

我让它在一个列上工作，这很简单。有什么想法吗？

Answer 1

我建议全文搜索(MS' 或 Lucene 都可以)下面的代码使用 MSSQL FTS 作为我目前在我的应用程序中使用的内容。

如果尚未安装 FTS 搜索，请安装。如果您检查过该服务正在运行。在 Management Studio 中运行此命令来设置目录并添加产品表；和颜色/名称/产品编号到目录。

USE [AdventureWorks]
GO
CREATE FULLTEXT CATALOG [ProductsTest]WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]

GO

USE [AdventureWorks]
GO
CREATE FULLTEXT INDEX ON [Production].[Product] KEY INDEX [PK_Product_ProductID] ON ([ProductsTest]) WITH (CHANGE_TRACKING AUTO)
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Color])
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Name])
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([ProductNumber])
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ENABLE
GO

然后您可以一次对所有列运行查询；例如银色(根据颜色和名称选择)

Select * from production.product where
contains(*, '"Silver*"')

查询中的*将找到Silver*，因此您可以在用户输入时使用它来构建结果。需要考虑的一件事是谷歌使这项工作实时进行 - 如果您正在搜索大量数据，能够在不中断用户打字的情况下取回数据。我认为通常人们通过从他们要查找的第一个字母开始输入来使用这些搜索 - 我接受会有拼写错误 - 你可以在他们按下的每个空格之后实现拼写检查器也许可以处理这个问题。或者存储运行的搜索并查看拼写错误并更改代码以基于映射(或在 FTS 中使用自定义同义词库来处理该问题。)

排名对于任何企业来说都是一个有趣的发展问题；您是否正在查找 Mountain Frame 的第一个结果 - 或者您想按销量或价格对它们进行加权？如果用户输入多个文本术语，您可以使用 FTS 根据搜索字符串生成排名。

select aa.rank, bb.* 
From containstable(production.product, *, '"Mountain" and "Silver*"') aa
inner join production.product bb
on aa.[key] = bb.productid
order by rank desc

这将返回 30 行；并根据用户输入的文本的权重来确定第一名记录。在任何一种情况下，您可能都希望添加编码排名来调整结果以满足您的业务需求 - 对价格最高的小部件 1 进行排名可能不是这样。这就是为什么您要存储人们搜索/点击的内容，以便稍后分析结果。

有一个非常好的language parser用于 .Net，将输入的 google 样式字符串查询转换为 FTS 支持的语言，从而使您熟悉使用您网站的任何 bool 搜索。

您可能还想添加一些群体智慧功能，通过审核用户输入的内容并最终访问并使用成功 map 来更改最终建议，使其真正与用户相关。

最后的建议是，如果这是一个商业网站，您可能需要查看 Easyask这是一个可怕的、伟大的自然语言处理器