問題描述

好的，使用 SQL Server 2008.在我的網(wǎng)頁上，我有一個帶有 jQ??uery-UI AutoComplete 的文本框.

現(xiàn)在我需要一個存儲過程來搜索來自文本框/自動完成 AJAX 調(diào)用的搜索字符串，并返回建議的"搜索字符串.我正在使用 AdventureWorks db 進(jìn)行測試(產(chǎn)品表)

例如，產(chǎn)品表包含產(chǎn)品名稱和產(chǎn)品編號(以及其他)列，我想根據(jù)用戶輸入返回建議的搜索字符串，他們可以在其中輸入產(chǎn)品名稱和/或產(chǎn)品編號.

我讓它在一個簡單的列中工作.有什么想法嗎?

我將建議全文搜索(MS 或 Lucene 可以工作) 下面的代碼使用 MSSQL FTS 作為我在我的應(yīng)用程序中使用的瞬間.

如果您還沒有安裝 FTS 搜索.如果你有檢查服務(wù)正在運(yùn)行.在管理工作室中運(yùn)行它來設(shè)置目錄并添加產(chǎn)品表；和顏色/名稱/產(chǎn)品編號到目錄.

使用 [AdventureWorks]走創(chuàng)建全文目錄 [ProductsTest]WITH ACCENT_SENSITIVITY = OFF授權(quán) [dbo]走使用 [AdventureWorks]走CREATE FULLTEXT INDEX ON [Production].[Product] KEY INDEX [PK_Product_ProductID] ON ([ProductsTest]) WITH (CHANGE_TRACKING AUTO)走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Color])走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Name])走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([ProductNumber])走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ENABLE走

然后您可以一次對所有列運(yùn)行查詢；例如銀色(選擇顏色和名稱)

Select * from production.product wherecontains(*, '"銀*"')

查詢中的 * 會找到 Silver*，因此您可以在用戶輸入時使用它來構(gòu)建結(jié)果.需要考慮的一件事是 google 實(shí)時進(jìn)行這項工作 - 如果您要搜索大量數(shù)據(jù)能夠在不中斷用戶打字的情況下取回數(shù)據(jù).我認(rèn)為通常人們通過從他們正在尋找的第一個字母開始輸入來使用這些搜索 - 我接受會有拼寫錯誤 - 你可以在他們按下的每個空格之后實(shí)施拼寫檢查器，也許可以處理這個問題.或者存儲運(yùn)行的搜索并查看拼寫錯誤并更改代碼以根據(jù)映射(或使用自定義同義詞庫在 FTS 中處理).

排名對任何企業(yè)來說都是一個有趣的發(fā)展問題；您是在尋找 Mountain Frame 的第一個結(jié)果 - 還是想按銷售額或價格對它們進(jìn)行加權(quán)?如果用戶輸入多個文本術(shù)語，您可以使用 FTS 根據(jù)搜索字符串生成排名.

選擇aa.rank, bb.*來自 containstable(production.product, *, '"Mountain" and "Silver*"') aa內(nèi)連接生產(chǎn).product bb在 aa.[key] = bb.productid按等級降序排列

這將返回 30 行；并根據(jù)用戶輸入的文本進(jìn)行權(quán)重來確定第一名記錄.在任何一種情況下，您都可能希望添加編碼排名來調(diào)整結(jié)果以滿足您的業(yè)務(wù)需求 - 對價格最高的小部件 1 進(jìn)行排名可能不是這樣.這就是為什么您要存儲人們搜索/點(diǎn)擊的內(nèi)容，以便稍后分析結(jié)果.

有一個非常好的語言解析器用于 .Net 進(jìn)行翻譯輸入到 FTS'able 語言中的谷歌樣式字符串查詢，可讓您熟悉使用您網(wǎng)站的任何布爾搜索.

您可能還想通過審核用戶輸入的內(nèi)容并最終訪問并使用成功地圖來更改最終建議，以使其真正與用戶相關(guān)，從而添加一些群體智慧功能.

最后的建議，如果這是一個商業(yè)網(wǎng)站，你可能想看看 Easyask，這是一個可怕的偉大的自然語言處理器>

Ok, using SQL Server 2008. On my web page I have a textbox with jQuery-UI AutoComplete hooked up.

Now I need a stored procedure to search across all columns of a single table(or multiple joined tables I suppose) for a search string coming from the textbox/autocomplete AJAX call, and return "suggested" search strings. I am using the AdventureWorks db for testing(Products table)

So for example, the product table has columns for product name and product number(among others) and I want to return suggested search strings based on user input where they may enter a product name and/or a product number.

I have it working across a single column which was simple. Any ideas?

I'm going to suggest full text search (MS' or Lucene will work) The code below use MSSQL FTS as its what I use in my app at the moment.

Install FTS Search if you haven't already. If you have check the service is running. In management studio run this to setup a catalog and add the products table; and Color / Name / Product Number to the catalog.

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

You can then run queries against all columns at once; e.g. Silver (Chosen as its in color and Name)

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

The * on the query will find Silver* so you can use this to build up results as the user types in. One thing to consider is that google make this work in real time - if you are searching a lot of data you to be able to get the data back without interrupting the typing of the user. i think generally people use these searches by typing from the first letter they are looking for - i accept there will be spelling mistakes- you could implement a spell checker after every space they press perhaps to handle that. Or store the searches that are run and look at the mispellings and change the code to handle that based on a mapping (or in FTS using a custom thesaurus.)

Ranking is going to be a fun development issue to any business; are you finding the first result for Mountain Frame -or do you want to weight them by sales or price? If the user types in more than one text term you can use FTS to produce a ranking based on the search string.

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

This returns 30 rows; and weights based on the user inputted text to determine the first place record. In either case you will likely want to add a coded ranking to tweak the results to suit your business desires - ranking te highest priced widget 1 might not be the way. That is why you are going to store what people searched for / clicked on so you can analyse the results later.

There is a really nice language parser for .Net that translates a google style string query inputted into FTS'able language which gives familiarity for any boolean searches that use your site.

You may also want to add some wisdom of crowds features by auditing against what users have input and ultimately gone to visit and use success maps to alter the final suggestions to actually make them relevant to the user.

As a final suggestion if this is a commercial website you might want to look at Easyask which is a scary great natural language processor

這篇關(guān)于T-SQL 存儲過程返回谷歌風(fēng)格的“建議"搜索結(jié)果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！