设为首页 |收藏本站 |论坛搜索
登录 注册 找回密码

站长论坛

 找回密码
 注册

QQ登录

只需一步，快速开始

全国各地医院查询重量转换换算RGB颜色查询交通标志大全各类快递查询
简体繁体转换黄金价格实时走势万年历查询实时汇率转换列车时刻查询
在线翻译工具CSS中文手册HTML学习教程MySQL中文手册JavaScript中文手册
PHP安全基础手册PHP5面向对象编程教程正则表达式系统教程SQL Server精华Apache 2.2 中文手册
DOS命令全集指南windows脚本技术中文版股票行情查询历史上的今天邮编区号查询
长度转换换算货币汇率转换常用电话号码体育彩票查询手机位置查询
域名Whois信息查询谷歌PR值查询台州网站建设台州网站开发台州域名注册
天气预报查询长度转换换算器在线电子地图车牌号码查询中国百家姓查询
返回列表 ...发表新帖
查看: 7347|回复: 1
打印 上一主题 下一主题

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

[复制链接]
跳转到指定楼层
1#
发表于 2007-10-7 22:25:56 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
台州网址导航
现在很多网站都提供了站内的搜索功能，有的很简单在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调用。函数如下：
  1. 1CREATE FUNCTION dbo.WordCount
  2. 2
  3. 3(@Word VARCHAR(15),
  4. 4
  5. 5@Phrase VARCHAR(1000))
  6. 6
  7. 7RETURNS SMALLINT
  8. 8
  9. 9AS
  10. 10
  11. 11BEGIN
  12. 12
  13. 13/**//* 如果@Word 或者@Phrase 为空返回 0 */
  14. 14
  15. 15IF @Word IS NULL OR @Phrase IS NULL RETURN 0
  16. 16
  17. 17/**//* @BiggerWord 比@Word长一个字符 */
  18. 18
  19. 19DECLARE @BiggerWord VARCHAR(21)
  20. 20
  21. 21SELECT @BiggerWord = @Word + 'x'
  22. 22
  23. 23/**//*在 @Phrase用@BiggerWord替换@Word */
  24. 24
  25. 25DECLARE @BiggerPhrase VARCHAR(2000)
  26. 26
  27. 27SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord)
  28. 28
  29. 29/**//* 相减结果就是出现的次数了 */
  30. 30
  31. 31RETURN LEN(@BiggerPhrase) - LEN(@Phrase)
  32. 32
  33. 33END
  34. 34
复制代码
以上就是整个UDF，它用了一个很高效的方法来计算关键词出现的次数。





二、参数传递

用户输入的关键词从一个到多个不等，我们可以把参数固定为@word1~@word5，这样比较方面实现。当用户输入超过5个时，忽略不计，少于5个的地方视为空。其实GOOGLE也是这样做的，只是GOOGLE的最大词语限制是10个。



三、搜索的实现过程



假定我们对Product表进行搜索，Product字段有：Id,Name ,Descripton(产品描述)，搜索要同时对Name 和 Description进行。

Any-World Search实现如下：
  1. 1SELECT Product.Name,
  2. 2       3 * WordCount(@Word1, Name) + WordCount(@Word1, Description) +
  3. 3
  4. 4       3 * WordCount(@Word2, Name) + WordCount(@Word2, Description) +
  5. 5
  6. 6      
  7. 7
  8. 8       AS Rank
  9. 9FROM Product
  10. 10
  11. 11
复制代码
这里对Name赋予权重为3，Description为1（大家根据实际情况赋予不同的权重），Rank是计算列，通过前面定义的UDF计算所关键词出现的次数乘上权重等到的。



同样的All-Word Search实现如下：

  2. 1 SELECT Product.Name,
  3. 2
  4. 3       (3 * WordCount(@Word1, Name) + WordCount(@Word1, Description)) *
  5. 4
  6. 5       CASE
  7. 6
  8. 7          WHEN @Word2 IS NULL THEN 1
  9. 8
  10. 9            ELSE 3 * WordCount(@Word2, Name) + WordCount(@Word2, Description)
  11. 10
  12. 11       END *
  13. 12
  14. 13      
  15. 14
  16. 15       AS Rank
  17. 16
  18. 17FROM Product
  19. 18
复制代码
这时把每个关键词出现的次数相乘只要一个没出现

RANK就为0，为0就是搜索结果为空。
Server, SQL, 百度, 搜索, 在SQL
分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 转播转播 分享分享 分享淘帖

相关帖子

台州维博网络(www.tzweb.com)专门运用PHP+MYSQL/ASP.NET+MSSQL技术开发网站门户平台系统等。
回复

使用道具 举报

2#
 楼主| 发表于 2007-10-7 22:26:08 | 只看该作者
台州网址导航
还可以这样实现：
  1. 1 SELECT Product.Name,
  2. 2       CASE
  3. 3         WHEN @Word1 IS NULL THEN 0
  4. 4         ELSE ISNULL(NULLIF(dbo.WordCount(@Word1, Name + ' ' + Description), 0), -1000)
  5. 5       END +
  6. 6       CASE
  7. 7         WHEN @Word2 IS NULL THEN 0
  8. 8         ELSE ISNULL(NULLIF(dbo.WordCount(@Word2, Name + ' ' + Description), 0), -1000)
  9. 9       END +
  10. 10      
  11. 11       AS Rank
  12. 12FROM Product
复制代码
对没出现的关键词赋值-1000，这样Rank就肯定为负数，负数表示搜索结果为空。


四、对结果进行分页

搜索的结果可能很多，对结果分页可以提高性能。我在如何在数据层分页以提高性能已经说明了如何用存储过程进行分页了，这里就不在详细复述了。

过程简单来说就是创建一个临时表，表中包含行号，读取时按行号来读取数据


五、完整代码

     经过前面的分析，完整代码如下：
  1.   1CREATE PROCEDURE SearchCatalog
  2.   2(      
  3.   3 @PageNumber TINYINT,
  4.   4 @ProductsPerPage TINYINT,
  5.   5 @HowManyResults SMALLINT OUTPUT,
  6.   6 @AllWords BIT,
  7.   7 @Word1 VARCHAR(15) = NULL,
  8.   8 @Word2 VARCHAR(15) = NULL,
  9.   9 @Word3 VARCHAR(15) = NULL,
  10. 10 @Word4 VARCHAR(15) = NULL,
  11. 11 @Word5 VARCHAR(15) = NULL)
  12. 12AS
  13. 13/**//* 创建临时表，保存搜索的结果（Sql Server2005适用，Sql Server2000见如何在数据层分页以提高性能） */
  14. 14DECLARE @Products TABLE
  15. 15(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,
  16. 16 ID INT,
  17. 17 Name VARCHAR(50),
  18. 18 Description VARCHAR(1000),
  19. 19Rank INT)
  20. 20
  21. 21/**//* Any-words search */
  22. 22IF @AllWords = 0
  23. 23   INSERT INTO @Products           
  24. 24   SELECT ID, Name, Description,
  25. 25        3 * dbo.WordCount(@Word1, Name) + dbo.WordCount(@Word1, Description) +
  26. 26
  27. 27        3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description) +
  28. 28
  29. 29        3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, Description) +
  30. 30
  31. 31        3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, Description) +
  32. 32
  33. 33        3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5, Description)
  34. 34
  35. 35          AS Rank
  36. 36
  37. 37   FROM Product
  38. 38   ORDER BY Rank DESC
  39. 39
  40. 40/**//* all-words search */
  41. 41
  42. 42IF @AllWords = 1
  43. 43
  44. 44   INSERT INTO @Products           
  45. 45
  46. 46   SELECT ID, Name, Description,
  47. 47
  48. 48          (3 * dbo.WordCount(@Word1, Name) + dbo.WordCount
  49. 49
  50. 50(@Word1, Description)) *
  51. 51
  52. 52          CASE
  53. 53
  54. 54           WHEN @Word2 IS NULL THEN 1
  55. 55
  56. 56           ELSE 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2,
  57. 57
  58. 58Description)
  59. 59
  60. 60          END *
  61. 61
  62. 62          CASE
  63. 63
  64. 64           WHEN @Word3 IS NULL THEN 1
  65. 65
  66. 66           ELSE 3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3,
  67. 67
  68. 68Description)
  69. 69
  70. 70          END *
  71. 71
  72. 72          CASE
  73. 73
  74. 74           WHEN @Word4 IS NULL THEN 1
  75. 75
  76. 76           ELSE 3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4,
  77. 77
  78. 78Description)
  79. 79
  80. 80          END *
  81. 81
  82. 82          CASE
  83. 83
  84. 84           WHEN @Word5 IS NULL THEN 1
  85. 85
  86. 86           ELSE 3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5,
  87. 87
  88. 88Description)
  89. 89
  90. 90          END
  91. 91
  92. 92          AS Rank
  93. 93
  94. 94   FROM Product
  95. 95
  96. 96   ORDER BY Rank DESC
  97. 97
  98. 98/**//* 在外部变量保存搜索结果数 */
  99. 99
  100. 100SELECT @HowManyResults = COUNT(*)
  101. 101
  102. 102FROM @Products
  103. 103
  104. 104WHERE Rank > 0
  105. 105
  106. 106/**//* 按页返回结果*/
  107. 107
  108. 108SELECT ProductID, Name, Description, Price, Image1FileName,
  109. 109
  110. 110 Image2FileName, Rank
  111. 111
  112. 112FROM @Products
  113. 113
  114. 114WHERE Rank > 0
  115. 115
  116. 116  AND RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1
  117. 117
  118. 118                    AND @PageNumber * @ProductsPerPage
  119. 119ORDER BY Rank DESC
复制代码
至此一个简单的搜索算法就实现了。
台州维博网络(www.tzweb.com)专门运用PHP+MYSQL/ASP.NET+MSSQL技术开发网站门户平台系统等。
回复 支持 反对

使用道具 举报

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则

网站推广
关于我们
  • 台州朗动科技(Tzweb.com)拥有多年开发网站平台系统门户手机客户端等业务的成功经验。主要从事：政企网站,系统平台,微信公众号,各类小程序,手机APP客户端,浙里办微应用,浙政钉微应用、主机域名、虚拟空间、后期维护等服务，满足不同企业公司的需求，是台州地区领先的网络技术服务商!

Hi,扫描关注我

Copyright © 2005-2026 站长论坛 All rights reserved

Powered by 站长论坛 with TZWEB Update Techonolgy Support

快速回复 返回顶部 返回列表