問(wèn)題描述

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

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

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

我讓它在一個(gè)簡(jiǎn)單的列中工作.有什么想法嗎?

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

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

使用 [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走

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

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

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

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

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

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

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

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

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

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 存儲(chǔ)過(guò)程返回谷歌風(fēng)格的“建議"搜索結(jié)果的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！