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

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

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

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

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

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

一、對搜索結果進行排序的算法

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

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/**//* 相減結果就是出現的次數了 */3031RETURN LEN(@BiggerPhrase) - LEN(@Phrase)3233END34

以上就是整個UDF，它用了一個很高效的方法來計算關鍵詞出現的次數。

二、參數傳遞

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

三、搜索的實現過程

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

Any-World Search實現如下：

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賦予權重為3，Description為1（大家根據實際情況賦予不同的權重），Rank是計算列，通過前面定義的UDF計算所關鍵詞出現的次數乘上權重等到的。

同樣的All-Word Search實現如下：

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

這時把每個關鍵詞出現的次數相乘只要一個沒出現RANK就為0，為0就是搜索結果為空。

還可以這樣實現：

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對沒出現的關鍵詞賦值-1000，這樣Rank就肯定為負數，負數表示搜索結果為空。

四、對結果進行分頁

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

過程簡單來說就是創建一個臨時表，表中包含行號，讀取時按行號來讀取數據

五、完整代碼

經過前面的分析，完整代碼如下：

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/**//* 創建臨時表，保存搜索的結果（Sql Server2005適用，Sql Server2000見如何在數據層分頁以提高性能） */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/**//* 在外部變量保存搜索結果數 */99100SELECT @HowManyResults = COUNT(*) 101102FROM @Products 103104WHERE Rank > 0105106/**//* 按頁返回結果*/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

至此一個簡單的搜索算法就實現了。