現在很多網站都提供了站內的搜索功能，有的很簡單在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.WordCount
2
3(@Word VARCHAR(15),
4
5@Phrase VARCHAR(1000))
6
7RETURNS SMALLINT
8
9AS
10
11BEGIN
12
13/**//* 如果@Word 或者@Phrase 為空返回 0 */
14
15IF @Word IS NULL OR @Phrase IS NULL RETURN 0
16
17/**//* @Biggerword 比@Word長一個字符 */
18
19DECLARE @BiggerWord VARCHAR(21)
20
21SELECT @Biggerword = @Word + 'x'
22
23/**//*在 @Phrase用@Biggerword替換@Word */
24
25DECLARE @BiggerPhrase VARCHAR(2000)
26
27SELECT @BiggerPhrase = REPLACE (@Phrase, @word, @BiggerWord)
28
29/**//* 相減結果就是出現的次數了 */
30
31RETURN LEN(@BiggerPhrase) - LEN(@Phrase)
32
33END
34
以上就是整個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) +
3
4 3 * wordCount(@word2, Name) + wordCount(@Word2, Description) +
5
6
7
8 AS Rank
9FROM Product
10
11
這裡對Name賦予權重為3，Description為1（大家根據實際情況賦予不同的權重），Rank是計算列，通過前面定義的UDF計算所關鍵詞出現的次數乘上權重等到的。
同樣的All-Word Search實現如下：
1SELECT Product.Name,
2
3 (3 * wordCount(@word1, Name) + wordCount(@Word1, Description)) *
4
5 CASE
6
7 WHEN @Word2 IS NULL THEN 1
8
9 ELSE 3 * wordCount(@word2, Name) + wordCount(@Word2, Description)
10
11 END *
12
13
14
15 AS Rank
16
17FROM Product
18
這時把每個關鍵詞出現的次數相乘只要一個沒出現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 Rank
12FROM 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)
12AS
13/**//* 創建臨時表，保存搜索的結果（Sql Server2005適用，SQL Server2000見如何在數據層分頁以提高性能） */
14DECLARE @Products TABLE
15(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,
16 ID INT,
17 Name VARCHAR(50),
18 Description VARCHAR(1000),
19Rank INT)
20
21/**//* Any-Words search */
22IF @AllWords = 0
23 INSERT INTO @Products
24 SELECT ID, Name, Description,
25 3 * dbo.wordCount(@word1, Name) + dbo.wordCount(@Word1, Description) +
26
27 3 * dbo.wordCount(@word2, Name) + dbo.wordCount(@Word2, Description) +
28
29 3 * dbo.wordCount(@word3, Name) + dbo.wordCount(@Word3, Description) +
30
31 3 * dbo.wordCount(@word4, Name) + dbo.wordCount(@Word4, Description) +
32
33 3 * dbo.wordCount(@word5, Name) + dbo.wordCount(@Word5, Description)
34
35 AS Rank
36
37 FROM Product
38 ORDER BY Rank DESC
39
40/**//* all-Words search */
41
42IF @AllWords = 1
43
44 INSERT INTO @Products
45
46 SELECT ID, Name, Description,
47
48 (3 * dbo.wordCount(@word1, Name) + dbo.WordCount
49
50(@Word1, Description)) *
51
52 CASE
53
54 WHEN @Word2 IS NULL THEN 1
55
56 ELSE 3 * dbo.wordCount(@word2, Name) + dbo.wordCount(@Word2,
57
58Description)
59
60 END *
61
62 CASE
63
64 WHEN @Word3 IS NULL THEN 1
65
66 ELSE 3 * dbo.wordCount(@word3, Name) + dbo.wordCount(@Word3,
67
68Description)
69
70 END *
71
72 CASE
73
74 WHEN @Word4 IS NULL THEN 1
75
76 ELSE 3 * dbo.wordCount(@word4, Name) + dbo.wordCount(@Word4,
77
78Description)
79
80 END *
81
82 CASE
83
84 WHEN @Word5 IS NULL THEN 1
85
86 ELSE 3 * dbo.wordCount(@word5, Name) + dbo.wordCount(@Word5,
87
88Description)
89
90 END
91
92 AS Rank
93
94 FROM Product
95
96 ORDER BY Rank DESC
97
98/**//* 在外部變量保存搜索結果數 */
99
100SELECT @HowManyResults = COUNT(*)
101
102FROM @Products
103
104WHERE Rank > 0
105
106/**//* 按頁返回結果*/
107
108SELECT ProductID, Name, Description, Price, Image1FileName,
109
110 Image2FileName, Rank
111
112FROM @Products
113
114WHERE Rank > 0
115
116 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1
117
118 AND @PageNumber * @ProductsPerPage
119ORDER BY Rank DESC
至此一個簡單的搜索算法就實現了。
