转：SQL LIKE语句多条件贪婪加权匹配算法(改进版)

原文转载自：http://blog.csdn.net/yangyuankp/article/details/8069325

 

直接入题，其他的就不罗嗦了。

上篇博客仅仅是实现了多个关键字“尽可能多的匹配”。

但是在实际应用中，尽可能多的匹配不一定合理。

就拿“如何在CSDN网站注册用户”这句话来说，拆成“CSDN”、“注册”、“用户”这三个词。假如数据库中某条记录匹配了“CSDN”、“注册”这两个词，另一条记录匹配了“注册”、“用户”这两个词。两条记录中匹配词的个数都是两个，如果仅仅用上一篇博客的算法，这两个记录是对等的，而显然，他们不是对等的！“CSDN”这个词在这句话中至关重要，他是先决条件。因此，匹配“CSDN”、“注册”这两个词的记录要比匹配“注册”、“用户”这两个词的记录优先。

基于以上事实，让我们清醒的意识到关键词并不是对等的，应该给他们分配一个权重，权重高的优先。
SQL LIKE语句多条件贪婪匹配加权改进版：

GO
CREATE function Get_StrArrayLength
(
 @str varchar(1024),  --要分割的字符串
 @split varchar(10)  --分隔符号
)
returns int
as
 begin
  declare @location int
  declare @start int
  declare @length int
  set @str=ltrim(rtrim(@str))
  set @location=charindex(@split,@str)
  set @length=1
   while @location<>0
     begin
      set @start=@location+1
      set @location=charindex(@split,@str,@start)
      set @length=@length+1
     end
   return @length
 end
 GO
 CREATE function Get_StrArrayStrOfIndex
(
 @str varchar(1024),  --要分割的字符串
 @split varchar(10),  --分隔符号
 @index int --取第几个元素
)
returns varchar(1024)
as
begin
 declare @location int
 declare @start int
 declare @next int
 declare @seed int
 set @str=ltrim(rtrim(@str))
 set @start=1
 set @next=1
 set @seed=len(@split)
 set @location=charindex(@split,@str)
 while @location<>0 and @index>@next
   begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
   end
 if @location =0 select @location =len(@str)+1
 
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
 return substring(@str,@start,@location-@start)
end
GO
CREATE PROCEDURE proc_Common_SuperLike
	--要查询的表的主键字段名称
	@primaryKeyName varchar(999),
	--要查询的表名
	@talbeName varchar(999),
	--要查询的表的字段名称，即内容所在的字段
	@contentFieldName varchar(999),
	--查询记录的个数(TOP *),匹配的个数越多,排名越靠前
	@selectNumber varchar(999),
	--匹配字符分隔标记
	@splitString varchar(999),
	--匹配字符组合字符串
	@words varchar(999)
	
AS
	declare @sqlFirst varchar(999)
	declare @sqlCenter varchar(999)
	declare @sqlLast varchar(999)
BEGIN
	set @sqlCenter=''
	declare @next int  
	declare @arrayLength int
	set @next=1
	set @arrayLength=dbo.Get_StrArrayLength(@words,@splitString)
	while @next<=@arrayLength
	begin
		--构造sql查询条件(中间部分)
		set @sqlCenter = @sqlCenter+'SELECT '+@primaryKeyName+','+CONVERT(varchar(999),@arrayLength-@next+1)+' AS wordPower FROM '+@talbeName+' WHERE '+@contentFieldName+' like ''%'+dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next)+'%'' UNION ALL '
		set @next=@next+1
	end
	--处理sql语句中间部分,去除最后无用语句
	set @sqlCenter=left(@sqlCenter,(len(@sqlCenter)-10))
	--构造sql语句开头部分
	set @sqlFirst='SELECT TOP '+@selectNumber+' '+@primaryKeyName+',COUNT(*)+SUM(wordPower) AS finalPower FROM ('
	--构造sql语句结尾部分
	set @sqlLast=') AS t_Temp GROUP BY '+@primaryKeyName+' ORDER BY finalPower DESC'
	--拼接出完整sql语句,并执行
	execute(@sqlFirst+@sqlCenter+@sqlLast)
END

 

调用方法和第一个版本一样：

execute proc_Common_SuperLike 'id','t_test','content','20','|','i|o|c'

id表的主键字段名称。
t_test表名。
content匹配内容字段名称。
20选出20个记录（从顶至下匹配度越来越低）。
|关键字的分隔符号。
i|o|c一共有i，o，c三个关键字，通过|分隔。
 
不同的是，关键字有了权重的概念。
规则：i|o|c这三个关键字，权重依次降低，i的权重是3，o的权重2，c的权重是1。
也就是说，把重要的关键字靠前放，不重要的关键字靠后放，越靠前权重越高。

 

需要说明的是：

本算法以权重+匹配数综合结果作为判断标准，而不再是一味的贪婪。
比如有a|b|c|d|e五个关键字，按照规则，权重分别是：a-5、b-4、c-3、d-2、e-1，假如某条记录匹配了a、b、c三个关键字，另一条记录匹配了b、c、d、e四个关键字。

依照算法：
第一条记录最终权重是5(a权重)+4(b权重)+3(c权重)+3(匹配数)=15
第二条记录最终权重是4(b权重)+3(c权重)+2(d权重)+1(e权重)+4(匹配数)=14
因此优先选择第一条记录，虽然第一条记录的匹配数没有第二条多，但最终权重高。