SQL Server 2005中用存储过程实现搜索功能

欢迎您访问我爱IT技术网，今天小编为你分享的编程技术是：【SQL Server 2005中用存储过程实现搜索功能】，下面是详细的分享！

SQL Server 2005中用存储过程实现搜索功能

现在很多网站都提供了站内的搜索功能，有的很简单在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 14 15IF @word IS NULL OR @Phrase IS NULL RETURN 0 16 17 18 19DECLARE @Biggerword VARCHAR(21) 20 21SELECT @Biggerword=@word + 'x' 22 23 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  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  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  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

 至此一个简单的搜索算法就实现了。

以上所分享的是关于SQL Server 2005中用存储过程实现搜索功能，下面是编辑为你推荐的有价值的用户互动：

　　相关问题：在SQL SERVER 2005中快速搜索触发器调用了哪个存储...

　　答：个人才疏学浅，只知道通过存储过程查调用了那些触发器的方式，脚本如下： exec sp_depends 存储过程名称 >>详细

　　相关问题：SQL SERVER 2005 存储过程中怎样使用 like.

　　答：create proc pp @a int,@b nvarchar(50) as declare @id int select @id=b from 表2 where 2=@a update 表1 set 2=@id where 3=@a and 其他条件=@b 执行传参 exec pp '1','aaa' >>详细

　　相关问题：sql2005 存储过程中where后面的 = 或 like 拼接

　　答：不太可能，一个是sql语句，一个是变量，怎么合起来？ >>详细