現(xiàn)在很多網(wǎng)站都提供了站內(nèi)的搜索功能，有的很簡單在SQL語句里加一個條件如：where names like ‘%words%’就可以實現(xiàn)最基本的搜索了。

我們來看看功能強大一點，復雜一點的搜索是如何實現(xiàn)的（在SQL　ＳＥＲＶＥＲ２００／２００５通過存儲過程實現(xiàn)搜索算法）。

我們把用戶的搜索可以分為以下兩種：

1.精確搜索,就是把用戶輸入的各個詞語當成一個整體,不分割搜索.

2.像百度,GOOGLE一樣的,按空格把輸入的每一個詞分離,只要包含這些詞語,而不管出現(xiàn)的順序，稱為ALL-Word Search.

3.對輸入的詞只要有一個出現(xiàn)就為匹配 稱為Any-Word Search

一、對搜索結(jié)果進行排序的算法

在前面提到的LIKE語句最大的問題就是搜索的結(jié)果是沒有經(jīng)過排序的,我們不知道結(jié)果出現(xiàn)在的順序是如何的,因為它是隨機的。像百度,GOOGLE都會對結(jié)果用算法進行排序再顯示的.好我們也來建立一個簡單的排序法。一個很常見的算法是計算關(guān)鍵詞在被搜索內(nèi)容中出現(xiàn)的次數(shù),次數(shù)最多的排在結(jié)果的第一位。我們的是在存儲過程中實現(xiàn)這個算法的，而在ＳＱＬSERVER中沒有提供計算關(guān)鍵詞在被搜索內(nèi)容中出現(xiàn)的次數(shù)這樣的函數(shù)，我們要自己寫一個UDF（User-Defined Functions）,UDF是SQLSERVER的內(nèi)部函數(shù)，可以被存儲過程調(diào)用或者被其他UDF調(diào)用。函數(shù)如下：

1CREATE FUNCTION dbo.WordCount23(@Word VARCHAR(15), 45@Phrase VARCHAR(1000))67RETURNS SMALLINT89AS1011BEGIN1213/**//* 如果@Word 或者@Phrase 為空返回 0 */1415IF @Word IS NULL OR @Phrase IS NULL RETURN 01617/**//* @BiggerWord 比@Word長一個字符 */1819DECLARE @BiggerWord VARCHAR(21)2021SELECT @BiggerWord = @Word + 'x'2223/**//*在 @Phrase用@BiggerWord替換@Word */2425DECLARE @BiggerPhrase VARCHAR(2000)2627SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord)2829/**//* 相減結(jié)果就是出現(xiàn)的次數(shù)了 */3031RETURN LEN(@BiggerPhrase) - LEN(@Phrase)3233END34

以上就是整個UDF，它用了一個很高效的方法來計算關(guān)鍵詞出現(xiàn)的次數(shù)。

二、參數(shù)傳遞

用戶輸入的關(guān)鍵詞從一個到多個不等，我們可以把參數(shù)固定為@word1~@word5，這樣比較方面實現(xiàn)。當用戶輸入超過5個時，忽略不計，少于5個的地方視為空。其實GOOGLE也是這樣做的，只是GOOGLE的最大詞語限制是10個。

三、搜索的實現(xiàn)過程

假定我們對Product表進行搜索，Product字段有：Id,Name ,Descripton(產(chǎn)品描述)，搜索要同時對Name 和 Description進行。

Any-World Search實現(xiàn)如下：

1SELECT Product.Name, 2;;;;3 * WordCount(@Word1, Name) + WordCount(@Word1, Description) +34;;;;3 * WordCount(@Word2, Name) + WordCount(@Word2, Description) +56;;;;78;;;;AS Rank9FROM Product1011

這里對Name賦予權(quán)重為3，Description為1（大家根據(jù)實際情況賦予不同的權(quán)重），Rank是計算列，通過前面定義的UDF計算所關(guān)鍵詞出現(xiàn)的次數(shù)乘上權(quán)重等到的。

同樣的All-Word Search實現(xiàn)如下：

1SELECT Product.Name, 23;;;;(3 * WordCount(@Word1, Name) + WordCount(@Word1, Description)) *45;;;;CASE 67; WHEN @Word2 IS NULL THEN 1 89;ELSE 3 * WordCount(@Word2, Name) + WordCount(@Word2, Description)1011;;;;END *1213;;;;1415;;;;AS Rank1617FROM Product18

這時把每個關(guān)鍵詞出現(xiàn)的次數(shù)相乘只要一個沒出現(xiàn)RANK就為0，為0就是搜索結(jié)果為空。

還可以這樣實現(xiàn)：

1SELECT Product.Name, 2;;;;CASE 3 WHEN @Word1 IS NULL THEN 0 4 ELSE ISNULL(NULLIF(dbo.WordCount(@Word1, Name + ' ' + Description), 0), -1000)5;;;;END +6;;;;CASE 7 WHEN @Word2 IS NULL THEN 0 8 ELSE ISNULL(NULLIF(dbo.WordCount(@Word2, Name + ' ' + Description), 0), -1000)9;;;;END +10;;;;11;;;;AS Rank12FROM Product對沒出現(xiàn)的關(guān)鍵詞賦值-1000，這樣Rank就肯定為負數(shù)，負數(shù)表示搜索結(jié)果為空。

四、對結(jié)果進行分頁

搜索的結(jié)果可能很多，對結(jié)果分頁可以提高性能。我在如何在數(shù)據(jù)層分頁以提高性能已經(jīng)說明了如何用存儲過程進行分頁了，這里就不在詳細復述了。

過程簡單來說就是創(chuàng)建一個臨時表，表中包含行號，讀取時按行號來讀取數(shù)據(jù)

五、完整代碼

經(jīng)過前面的分析，完整代碼如下：

1CREATE PROCEDURE SearchCatalog 2(;;; 3 @PageNumber TINYINT, 4 @ProductsPerPage TINYINT, 5 @HowManyResults SMALLINT OUTPUT, 6 @AllWords BIT, 7 @Word1 VARCHAR(15) = NULL, 8 @Word2 VARCHAR(15) = NULL, 9 @Word3 VARCHAR(15) = NULL,10 @Word4 VARCHAR(15) = NULL,11 @Word5 VARCHAR(15) = NULL)12AS13/**//* 創(chuàng)建臨時表，保存搜索的結(jié)果（Sql Server2005適用，Sql Server2000見如何在數(shù)據(jù)層分頁以提高性能） */14DECLARE @Products TABLE15(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,16 ID INT,17 Name VARCHAR(50),18 Description VARCHAR(1000),19Rank INT)2021/**//* Any-words search */22IF @AllWords = 0 23INSERT INTO @Products24SELECT ID, Name, Description,25;;;;;3 * dbo.WordCount(@Word1, Name) + dbo.WordCount(@Word1, Description) +2627;;;;;3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description) +2829;;;;;3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, Description) +3031;;;;;3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, Description) +3233;;;;;3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5, Description) 3435; AS Rank3637FROM Product38ORDER BY Rank DESC3940/**//* all-words search */4142IF @AllWords = 14344INSERT INTO @Products4546SELECT ID, Name, Description,4748; (3 * dbo.WordCount(@Word1, Name) + dbo.WordCount4950(@Word1, Description)) *5152; CASE 5354WHEN @Word2 IS NULL THEN 1 5556ELSE 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, 5758Description)5960; END *6162; CASE 6364WHEN @Word3 IS NULL THEN 1 6566ELSE 3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, 6768Description)6970; END *7172; CASE 7374WHEN @Word4 IS NULL THEN 1 7576ELSE 3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, 7778Description)7980; END *8182; CASE 8384WHEN @Word5 IS NULL THEN 1 8586ELSE 3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5, 8788Description)8990; END9192; AS Rank9394FROM Product9596ORDER BY Rank DESC9798/**//* 在外部變量保存搜索結(jié)果數(shù) */99100SELECT @HowManyResults = COUNT(*) 101102FROM @Products 103104WHERE Rank > 0105106/**//* 按頁返回結(jié)果*/107108SELECT ProductID, Name, Description, Price, Image1FileName,109110 Image2FileName, Rank111112FROM @Products113114WHERE Rank > 0115116; AND RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1 117118;AND @PageNumber * @ProductsPerPage119ORDER BY Rank DESC

至此一個簡單的搜索算法就實現(xiàn)了。