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

最新推荐文章于 2024-02-18 11:11:05 发布
最新推荐文章于 2024-02-18 11:11:05 发布
阅读量1.8w 收藏 17
点赞数 2
分类专栏： 技术文章 算法 文章标签： sql 百度 数据库 function 算法 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yangyuankp/article/details/8085514
版权

参考博文：

 

         SQL LIKE语句多条件贪婪匹配算法

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

         SQL LIKE语句多条件贪婪加权匹配(新增必要词指定)

 

前言：


         算法的基本特性在前几篇博客中已经做了详细的说明，经过不断的改进优化，到归仓的时候了，也就是说，该算法告一段落，不再更新。

         作为最终的解决方案，简要的总结一下算法特性，以方便读者参阅。

        

         l  目的：主要用于多条件模糊匹配。

         l  贪婪特性：返回满足条件尽可能多的记录。

         l  权重特性：为关键词分配权重，代表关键词的重要性，在不破坏贪婪特性的前提下，返回权重高的记录。

         l  必要关键词指定特性：在不破坏贪婪特性和权重特性的前提下，返回的结果中必须包含指定的关键词。

         l  典型应用：问-答系统，例如百度提问、京东商品咨询。

 

         经过分析，在最终的解决方案中，提供两个版本的算法，已经封装成存储过程和函数，直接导入数据库即可。

 

普通版本：

 

         l  描述：基于SQL的LIKE语句实现，使用简单，但受限于LIKE语句，不适合超大数据量处理。指定必要词会加快处理速度。

         l  使用范围：万级别的数据量，数据量超过1万条，将导致运行缓慢。

         l  使用方法：直接在查询分析器中运行脚本导入数据库即可。

         l  调用示例：execute proc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'

         l  参数说明：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

大数据量版本：

 

         l  描述：基于SQL的全文索引实现，使用较为复杂，但执行速度极快，适合处理大数据量。指定必要词会降低处理速度。

         l  使用范围：千万级别的数据量，i3一代笔记本处理器，查询1千万条记录仅需2秒。

         l  使用方法：在查询分析器中运行脚本导入数据库，再为要查询的表创建全文索引，索引字段设置为要查询的字段。

         l  调用示例：execute proc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'

         l  参数说明：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

 

         注意：如果在创建数据库表全文索引之前，数据库表中已经有大量记录，那么创建全文索引是需要时间的，因此创建完全文索引后马上使用可能查不到数据。



yangyuankp
关注
  • 2
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
SQL多条件联合查询.txt
06-18
SQL同一个表不同条件两个SUM两个count查询结果一次查询并显示
SQL Server多表查询优化方案集锦
09-10
本文我们主要对SQL Server多表查询的优化方案进行了总结，并给出了实际的例子进行性能与效率的对比,需要的朋友可以参考下
SQL实现模糊查询的四种方法总结
最新发布
qq_58626489的博客
02-18 5624
SQL实现模糊查询的四种方法总结
SqlServer 巧妙解决多条件组合查询
09-11
开发中经常会遇得到需要多种条件组合查询的情况，比如有三个表，年级表Grade(GradeId,GradeName)，班级Class(ClassId,ClassName,GradeId)，学员表Student(StuId,StuName,ClassId)，现要求可以按年级Id、班级Id、学生名，这三个条件可以任意组合查询学员信息
OMG ❗ 快进来看看，我竟然发现了 ❗ ❗ ❗ MyBatis-Plus在写SQL时，like和like concat()的区别【CRUD番外篇】
小名同学
08-21 3万+
⚠⚠⚠自信攻城狮小名又来攻城啦⚠⚠⚠ 相信看到这篇文章的小伙伴已经悉数看过小名前两篇”正儿八经“的文章了吧？如果没看过，还不去看看 ❗ ❗ ❗ 1. 一篇朴实无华的MyBatis-Plus小白看完原地进阶的文章 2. 什么？mybatis-plus的多表查询，你还在写sql语句？！ 3. 此专栏下其他文章 今天这篇文章，是小名在写前两篇文章时发现的一个小问题，小名分享出来，大家都看一下吧~ 数据表 practice_user： 小名发现在 PracticeUserMapper.xml中： <se
Trie树-提高海量数据的模糊查询性能
渡码的专栏
04-27 454
今天这篇文章源于上周在工作中解决的一个实际问题，它是个比较普遍的问题，无论做什么开发，估计都有遇到过。具体是这样的，我们有一份高校的名单（2657个），需要从海量的文章标题中找到包含这些高校的标题，其实就是模糊查询。（关注公众号 渡码，回复关键词 trie 获取完整源代码）对应的伪代码如下 selected_titles = [] for 标题 in 海量标题： for 高校 in 高校名...
【sql: 每天进步一点点】sql 中的 LIKE 用法
weixin_30443731的博客
08-09 1034
like： like一般用在 where 表达式中，已搜索匹配子弹中的指定内容 like 通常与通配符 % 一起使用，%表示通配pattern中出现的内容，而不加通配符%的LIKE语法，表示精确匹配，其实际效果等同于 = 等于运算符 举例：查询姓李的老师，这是使用了通配符 不使用通配符，表示精准查找： 通配符 % 放在前面的表示以 什么什么结尾 匹配，%小，...
SQL语法——LIKE
Alittlevegetable的博客
11-09 4307
LIKE 操作符在SQL语法中是用在 WHERE 中的选取数据时的指定模式。 1选取开头包含某项 例如下面这条语句就是选取数据表chengjibiao中的score是以9开头的所有数据结果 SELECT * FROM chengjibiao WHERE score LIKE '9%'; 运行结果如下： 其中% 符号是用在模式的前后，是定义通配符。 2选取结尾包含某项 当需要选取数据表che...
sql中一张表的ids字段存储多个id用’,‘隔开，存储对应多张表的id，一对多关联查询
weixin_45121502的博客
07-06 2546
商品表：goods SELECT * FROM `goods` id name color_ids type_ids 1 铅笔 1,2,3 1,2 2 钢笔 3,4 1,3 颜色表：color SELECT * FROM color id color 1 黑色 2 白色 3 蓝色 4 紫色 类型表：type SELECT * from type id type 1 标配 2 标配+橡皮 3 标配+墨水 多表查询： SE
SQL多表联合查询时采用字段模糊匹配
Jeremiah_的博客
10-29 1万+
先说一下背景和要求： 背景：由于业务或是其他不描述的原因的问题导致原有存储的数据发生变动，与现有数据有差别，但还是能勉强看明白数据内容。 要求：实现A表的名称字段和B表的名称字段要模糊匹配。 上图： 假如A表长这样： B表长这样： 然后我要想变成这样： 简单说就是在我关联查询两表时，条件字段的取值看起来不一样，但是意思是一样的，应该要把这种数据关联起来。但是SQL里面“=”两边又必须严格相同，所以现在怎么办呢？ 方法一： 可以采用类似于LIKE模糊查询的办法。 MySQL：
SqlServer使用 case when 解决多条件模糊查询问题
09-10
主要介绍了SqlServer使用 case when 解决多条件模糊查询问题 的相关资料,需要的朋友可以参考下
如何写出高质量效率高的sql语句，看完这篇就懂了
davidiao的专栏
06-01 962
本文主要从工作经验中总结出来的经验总结sql语句优化问题，下面我们用Demo来具体说明如何提高sql的执行效率： 1、关于limit分页优化的问题 SELECT * FROM message_1 LIMIT 10000,10 这条语句执行速度很快，当我们把语句改成下面语句的时候再看看用了多久？ SELECT * FROM message_1 LIMIT 1000000,10 运行结果如下图： 上面的语句整整用了17.7秒！这么慢，用户可是等不了了，那么我们如何优化这句sql呢？ 解决方
sql语句中多个字段like模糊查询，按匹配度排序
☆╮123☆
02-24 1万+
like模糊查询，按匹配度排序 select * ,((case when name like '关键字%' then 4 else 0 end)+ (case when name like '%关键字%' then 3 else 0 end)) as namesort ,((case when content like '关键字%' then 2 else 0 end)+ (case ...
Mysql中正则表达式Regexp常见用法
fwj_ntu的博客
09-29 5850
Mysql中Regexp常见用法 模糊匹配，包含特定字符串 # 查找content字段中包含“车友俱乐部”的记录 select * from club_content where content regexp '车友俱乐部' # 此时的regexp与like的以下用法是等同的 select * from club_content where content like '%车友俱乐部%' ...
sql 条件查询以及模糊查询
zzj78778的博客
11-20 2270
sql 条件查询以及模糊查询
Mybatis 动态SQL
凉亭的博客
08-18 158
可以方便的在 sql 语句中实现某些逻辑. 总体说来mybatis 动态SQL 语句主要有以下几类: 1. if 语句 (简单的条件判断)2. where (主要是用来简化sql语句中where条件判断的，能智能的处理 and or ,不必担心多余导致语法错误) 3. choose (when,otherwize) ,与 jstl 中的choose 很类似.满足一个条件后其他分支就不再执行 4....
sql中like的所有用法
热门推荐
wsx1212123的博客
05-21 3万+
在sql结构化查询语言中，like语句有着至关重要的作用。 like语句的语法格式是：select * from 表名 where 字段名 like 对应值（子串）， 它主要是针对字符型字段的，它的作用是在一个字符型字段列中检索包含对应子串的。 A：% 包含零个或多个字符的任意字符串： 1、like’Mc%’ 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。 2、like’%inger’ 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。 3、like’%en%
html5多个图片位置_前端设计-教你如何快速绘制HTML5动画
weixin_39806065的博客
10-21 299
本周在给学生讲授JavaScript课程中setInterval方法时，想到了我们在网上看到的各类GIF动图比较有意思，就将其引入到教学中，教学内容设计如果通过HTML5与JavaScript实现网页类似GIF效果的动态图。于是在网上搜集了部分gif格式动图，将每一个Gif动图分解为一组jpg格式图片，再采用setInterval与html5 Canvas进行动画的实现。通过学习可以让学生进一步了...
多条件模糊查询sql语句
05-05
多条件模糊查询的 SQL 语句可以使用 `LIKE` 运算符和通配符 `%` 来实现。 例如，假设我们有一个名为 `users` 的表，其中包含 `id`、`name` 和 `email` 列。我们想要查询名字中包含 "John"，且邮箱中包含 "gmail" 的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值