`

转：SQL多条件模糊查询解决方案(类似百度搜索)

SQL多条件模糊查询解决方案(类似百度搜索) 
阅读更多

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

 

前言：
算法的基本特性在前几篇博客中已经做了详细的说明，经过不断的改进优化，到归仓的时候了，也就是说，该算法告一段落，不再更新。
作为最终的解决方案，简要的总结一下算法特性，以方便读者参阅。      

目的：主要用于多条件模糊匹配。
贪婪特性：返回满足条件尽可能多的记录。
权重特性：为关键词分配权重，代表关键词的重要性，在不破坏贪婪特性的前提下，返回权重高的记录。
必要关键词指定特性：在不破坏贪婪特性和权重特性的前提下，返回的结果中必须包含指定的关键词。
典型应用：问-答系统，例如百度提问、京东商品咨询。
 
经过分析，在最终的解决方案中，提供两个版本的算法，已经封装成存储过程和函数，直接导入数据库即可。

 

普通版本：

描述：基于SQL的LIKE语句实现，使用简单，但受限于LIKE语句，不适合超大数据量处理。指定必要词会加快处理速度。
使用范围：万级别的数据量，数据量超过1万条，将导致运行缓慢。
使用方法：直接在查询分析器中运行脚本导入数据库即可。
调用示例：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是必要词。

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)
	declare @next int  
	declare @arrayLength int
	declare @newWords varchar(999)
	declare @newTable varchar(999)
BEGIN
	set @newTable=@talbeName
	set @newWords=@words
	set @next=dbo.Get_StrArrayLength(@words,'[')
	--判断是否有必要词
	if @next>1
	begin
		set @newTable=''
		--构造必要表sql语句
		while @next>1
		begin
			set @newTable=@newTable+@contentFieldName+' like ''%'+dbo.Get_StrArrayStrOfIndex(dbo.Get_StrArrayStrOfIndex(@words,'[',@next),']',1)+'%'' AND '
			set @next=@next-1
		end
		set @newTable=left(@newTable,(len(@newTable)-4))
		--构造临时表
		set @newTable='SELECT * into ##tempTable FROM '+ @talbeName + ' WHERE ' + @newTable
		execute(@newTable)
		--指定临时表
		set @newTable='##tempTable'
		--去掉关键词组中的必要词标记
		set @newWords=REPLACE(REPLACE(@words,'[',''),']','')
	end
	set @sqlCenter=''
	set @next=1
	set @arrayLength=dbo.Get_StrArrayLength(@newWords,@splitString)

	while @next<=@arrayLength
	begin
		--构造sql查询条件(中间部分)
		set @sqlCenter = @sqlCenter+'SELECT '+@primaryKeyName+','+CONVERT(varchar(999),@arrayLength-@next+1)+' AS wordPower FROM '+@newTable+' WHERE '+@contentFieldName+' like ''%'+dbo.Get_StrArrayStrOfIndex(@newWords,@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)
	--判断临时表是否存在，存在则删除，一定要删除！
	if OBJECT_ID('tempDb..##tempTable') is not null
	begin
		drop table ##tempTable
	end
END

 

大数据量版本：

描述：基于SQL的全文索引实现，使用较为复杂，但执行速度极快，适合处理大数据量。指定必要词会降低处理速度。
使用范围：千万级别的数据量，i3一代笔记本处理器，查询1千万条记录仅需2秒。
使用方法：在查询分析器中运行脚本导入数据库，再为要查询的表创建全文索引，索引字段设置为要查询的字段。
调用示例：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是必要词。

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)
	declare @next int  
	declare @arrayLength int
	declare @newTable varchar(999)
BEGIN
	set @newTable=''
	set @sqlCenter=''
	set @next=1
	set @arrayLength=dbo.Get_StrArrayLength(@words,@splitString)

	while @next<=@arrayLength
	begin
		--构造sql查询条件(中间部分)
		--判断是否是必要词
		if CHARINDEX('[',dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next))>0
		begin
			set @sqlCenter = @sqlCenter+'SELECT '+@primaryKeyName+','+CONVERT(varchar(999),@arrayLength-@next+1)+' AS wordPower FROM '+@talbeName+' WHERE CONTAINS(' + @contentFieldName + ',''"*'+REPLACE(REPLACE(dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next),'[',''),']','')+'*"'') UNION ALL '
			--构造必要词
			set @newTable=@newTable+'CONTAINS(' + @contentFieldName + ',''"*'+REPLACE(REPLACE(dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next),'[',''),']','')+'*"'') AND '
		end
		else
		begin
			set @sqlCenter = @sqlCenter+'SELECT '+@primaryKeyName+','+CONVERT(varchar(999),@arrayLength-@next+1)+' AS wordPower FROM '+@talbeName+' WHERE CONTAINS(' + @contentFieldName + ',''"*'+dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next)+'*"'') UNION ALL '
		end
		
		set @next=@next+1
	end
	--判断是否有必要词
	if CHARINDEX('[',@words)>0
	begin
		---处理必要词部分,去除最后无用语句
		set @newTable=left(@newTable,(len(@newTable)-4))
		set @newTable='AS t_Temp WHERE '+ @primaryKeyName +' IN (SELECT '+@primaryKeyName+' FROM ' + @talbeName+' WHERE ' + @newTable + ')'
	end
	else
	begin
		set @newTable='AS t_Temp'
	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=') ' + @newTable + ' GROUP BY '+@primaryKeyName+' ORDER BY finalPower DESC'
	--拼接出完整sql语句,并执行
	Execute(@sqlFirst+@sqlCenter+@sqlLast)
END

 

附-SQL数据库表全文索引创建指南：

--开启全文索引
sp_fulltext_database enable

--创建索引目录(创建出来是一个目录，用来放索引文件)
CREATE FULLTEXT CATALOG 索引目录名称  --例如myFullText

--创建全文索引
CREATE FULLTEXT INDEX ON 表名(字段名)   --为哪个表的哪个字段创建全文索引，例如t_test(content)

KEY INDEX 主键索引名称 ON 索引目录名称  --注意是主键索引名称，而不是主键字段名称！例如，PK__t_test__3213E83F0EA330E9；指定全文索引目录，即放在哪个目录下，例如myFullText
 
注意：如果在创建数据库表全文索引之前，数据库表中已经有大量记录，那么创建全文索引是需要时间的，因此创建完全文索引后马上使用可能查不到数据。

分享到：
| 转：SQL LIKE语句多条件贪婪加权匹配算法( ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    JAVA的多条件模糊查询

    同样采用了动态SQL构建的方式，并且通过精确匹配实现了多条件查询。 #### 四、总结 通过上述示例我们可以看到，在Java中实现多条件模糊查询并不复杂，关键在于动态构建SQL语句以及正确使用`JdbcTemplate`进行参数...

    多条件模糊匹配搜索--模糊查询

    ### 多条件模糊匹配搜索——模糊查询 #### 一、概览 在现代网络应用中，搜索功能是一项不可或缺的核心功能，无论是论坛中的帖子搜索、新闻系统的文章搜索，还是下载站点的资源搜索，都需要一个强大而灵活的搜索...

    Sqlserver 2008模糊查询

    sqlserver 2008 模糊查询和联表查询

    SQL使用Like模糊查询

    在SQL（Structured Query Language）中，`LIKE` 模糊查询是一种非常实用的功能，它允许用户根据特定的模式匹配数据库中的数据。这个功能在数据检索、分析和处理时尤其有用，尤其是在我们不确定确切的搜索词或者需要...

    sql模糊查询并按匹配度排序

    ### SQL模糊查询并按匹配度排序 在数据库查询操作中，模糊查询是非常常见的一种需求，尤其是在需要处理大量数据且查询条件不确定的情况下。本篇文章将基于提供的SQL查询语句，详细介绍如何进行SQL模糊查询以及如何...

    sql server数据库模糊查询语句

    SQL Server 模糊查询语句是指使用 LIKE 关键字和通配符来搜索符合指定模式的字符串。下面是 SQL Server 模糊查询语句的详细说明： 1. LIKE 关键字：LIKE 关键字用于搜索符合指定模式的字符串。它可以与通配符结合...

    sql\（很长的l模糊查询拼接）sql存储过程

    - **多条件查询**：当存在多个查询条件时，可以通过IF语句组合不同的查询逻辑。 - **示例**： ```sql -- 当addDate和name都有值时 IF (@addDate IS NOT NULL) AND (@name &lt;&gt; '') BEGIN SELECT * FROM table ...

    JAVA，多条件，模糊查询，多个条件同时查询显示结果

    在Java编程中，进行多条件和模糊查询是数据库操作中常见的需求，特别是在处理用户输入时，用户可能希望根据多个不同的标准来筛选数据。这个场景通常涉及到SQL查询的编写，利用Java来执行这些查询，并将结果展示给...

    asp textbox 类似百度模糊查询功能

    在ASP（Active Server Pages）开发中，我们经常需要实现类似百度搜索框的模糊查询功能，让用户在输入框中键入关键词时，系统能够实时显示匹配的建议数据。这个功能极大地提高了用户体验，尤其对于大型数据集的搜索...

    查询条件是text的解决办法-sql sql text 数据类型为text解决办法

    ### 查询条件是text的数据类型解决办法 - SQL 在SQL中处理`text`数据类型时，经常会遇到各种挑战，尤其是在查询时。本文将详细介绍如何解决当查询条件涉及`text`数据类型时的问题，并给出具体的解决方案。 #### 1....

    织梦sql标签模糊查询+sql标签分页

    除了基础的模糊查询和分页，还可以利用SQL标签实现更多高级功能，比如按时间排序、条件组合查询等，为网站提供更多定制化的信息展示方式。 总之，理解和熟练运用织梦CMS的SQL标签及模糊查询、分页功能，能极大地...

    Microsoft SQL Server 2008技术内幕：T-SQL查询.pdf

    《Microsoft SQL Server 2008技术内幕：T-SQL查询》是一本深入探讨SQL Server 2008中T-SQL（Transact-SQL）查询的权威指南。T-SQL是微软SQL Server数据库管理系统的核心语言，用于数据操作、查询、存储过程编写以及...

    SQL高级实例-模糊查询-分组随机查询-转换

    首先，**模糊查询**是SQL中用于处理不精确或部分匹配搜索的关键技术。它主要通过`LIKE`、`NOT LIKE`、`ILIKE`（不区分大小写）和`REGEXP`等操作符实现。例如，`LIKE '%abc%'`将找到包含"abc"的所有字符串，而`REGEXP...

    SQL模糊查询

    ### SQL模糊查询详解 在日常的数据处理工作中，我们经常需要对数据库中的...无论是简单的关键词搜索还是复杂的多条件组合查询，`LIKE`关键字都能够提供灵活且强大的支持。希望本文对你理解和掌握SQL模糊查询有所帮助。

    Sql1使用模糊查询学生相关信息.sql

    Sql1使用模糊查询学生相关信息.sqlSql1使用模糊查询学生相关信息.sqlSql1使用模糊查询学生相关信息.sqlSql1使用模糊查询学生相关信息.sql

    SQL模糊查询语法小结

    ### SQL模糊查询语法详解...本文详细介绍了SQL模糊查询的基本语法及其常用的通配符，同时针对通配符导致查询无效的问题提供了解决方案。通过理解并熟练掌握这些技巧，可以帮助我们在实际工作中更加高效地进行数据检索。

    sql模糊查询的学习

    在SQL查询中，模糊查询是一种强大的工具，它允许我们寻找数据表中符合特定模式的记录。在本篇文章中，我们将深入探讨SQL模糊查询的核心概念，包括`LIKE`关键字、通配符以及`ESCAPE`子句的应用。 首先，`LIKE`关键字...

    c#模糊查询之sql语句

    本主题聚焦于“C#模糊查询之SQL语句”，这是一个关于如何在C#程序中利用SQL进行模糊查询的关键知识点。模糊查询是数据库检索中的一个重要功能，允许用户输入部分关键词或通配符来搜索匹配的数据，这对于数据量庞大的...

    基于SQL Server的多条件模糊匹配查询.pdf

    本文详细介绍了如何在SQL Server中实现多条件模糊匹配查询，这种方法在数据库查询和信息检索领域中非常重要，尤其是在复杂搜索条件下的应用。文章分别对单条件模糊匹配查询和多条件搜索的方法进行了阐述，并提出两种...

    sql模糊查询

    本文将基于给定的文件信息——“SQL模糊查询”这一主题，进行深入探讨，涵盖模糊查询的基本概念、应用场景、实现方法及其在C#中的具体应用。 ### SQL模糊查询基本概念 SQL模糊查询是指在SQL查询语句中，利用通配符...

Magicbox
Global site tag (gtag.js) - Google Analytics