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

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

《SQL多条件模糊查询解决方案类似百度搜索.docx》由会员分享，可在线阅读，更多相关《SQL多条件模糊查询解决方案类似百度搜索.docx（11页珍藏版）》请在冰豆网上搜索。

SQL多条件模糊查询解决方案类似XX搜索

SQL多条件模糊查询解决方案（类似XX搜索）

SQL多条件模糊查询解决方案（类似XX搜索）

时间：

2012-10-1816:

22:

32点击：

377

　　核心提示：

前言：

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

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

l目的：

...前言：

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

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

l目的：

主要用于多条件模糊匹配。

l贪婪特性：

返回满足条件尽可能多的记录。

l权重特性：

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

l必要关键词指定特性：

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

l典型应用：

问-答系统，例如XX提问、京东商品咨询。

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

普通版本：

l描述：

基于SQL的LIKE语句实现，使用简单，但受限于LIKE语句，不适合超大数据量处理。

指定必要词会加快处理速度。

l使用范围：

万级别的数据量，数据量超过1万条，将导致运行缓慢。

l使用方法：

直接在查询分析器中运行脚本导入数据库即可。

l调用示例：

executeproc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'

l参数说明：

id表的主键字段名称。

t_test表名。

content匹配内容字段名称。

20选出20个记录（从顶至下匹配度越来越低）。

|关键字的分隔符号。

[i]|o|c一共有i，o，c三个关键字，通过|分隔，其中i是必要词。

[sql]viewplaincopyprint?

GO

CREATEfunctionGet_StrArrayLength

（

@strvarchar（1024）,--要分割的字符串

@splitvarchar（10）--分隔符号

）

returnsint

as

begin

declare@locationint

declare@startint

declare@lengthint

set@str=ltrim（rtrim（@str））

set@location=charindex（@split,@str）

set@length=1

while@location<>0

begin

set@start=@location1

set@location=charindex（@split,@str,@start）

set@length=@length1

end

return@length

end

GO

CREATEfunctionGet_StrArrayStrOfIndex

（

@strvarchar（1024）,--要分割的字符串

@splitvarchar（10）,--分隔符号

@indexint--取第几个元素

）

returnsvarchar（1024）

as

begin

declare@locationint

declare@startint

declare@nextint

declare@seedint

set@str=ltrim（rtrim（@str））

set@start=1

set@next=1

set@seed=len（@split）

set@location=charindex（@split,@str）

while@location<>0and@index>@next

begin

set@start=@location@seed

set@location=charindex（@split,@str,@start）

set@next=@next1

end

if@location=0select@location=len（@str）1

--这儿存在两种情况：

1、字符串不存在分隔符号2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。

returnsubstring（@str,@start,@location-@start）

end

GO

CREATEPROCEDUREproc_Common_SuperLike

--要查询的表的主键字段名称

@primaryKeyNamevarchar（999）,

--要查询的表名

@talbeNamevarchar（999）,

--要查询的表的字段名称，即内容所在的字段

@contentFieldNamevarchar（999）,

--查询记录的个数（TOP*）,匹配的个数越多,排名越靠前

@selectNumbervarchar（999）,

--匹配字符分隔标记

@splitStringvarchar（999）,

--匹配字符组合字符串

@wordsvarchar（999）

AS

declare@sqlFirstvarchar（999）

declare@sqlCentervarchar（999）

declare@sqlLastvarchar（999）

declare@nextint

declare@arrayLengthint

declare@newWordsvarchar（999）

declare@newTablevarchar（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##tempTableFROM'@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-@next1）'ASwordPowerFROM'@newTable'WHERE'@contentFieldName'like''%'dbo.Get_StrArrayStrOfIndex（@newWords,@splitString,@next）'%''UNIONALL'

set@next=@next1

end

--处理sql语句中间部分,去除最后无用语句

set@sqlCenter=left（@sqlCenter,（len（@sqlCenter）-10））

--构造sql语句开头部分

set@sqlFirst='SELECTTOP'@selectNumber''@primaryKeyName',COUNT（*）SUM（wordPower）ASfinalPowerFROM（'

--构造sql语句结尾部分

set@sqlLast='）ASt_TempGROUPBY'@primaryKeyName'ORDERBYfinalPowerDESC'

--拼接出完整sql语句,并执行

Execute（@sqlFirst@sqlCenter@sqlLast）

--判断临时表是否存在，存在则删除，一定要删除！

ifOBJECT_ID（'tempDb..##tempTable'）isnotnull

begin

droptable##tempTable

end

END大数据量版本：

l描述：

基于SQL的全文索引实现，使用较为复杂，但执行速度极快，适合处理大数据量。

指定必要词会降低处理速度。

l使用范围：

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

l使用方法：

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

l调用示例：

executeproc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'

l参数说明：

id表的主键字段名称。

t_test表名。

content匹配内容字段名称。

20选出20个记录（从顶至下匹配度越来越低）。

|关键字的分隔符号。

[i]|o|c一共有i，o，c三个关键字，通过|分隔，其中i是必要词。

[sql]viewplaincopyprint?

GO

CREATEfunctionGet_StrArrayLength

（

@strvarchar（1024）,--要分割的字符串

@splitvarchar（10）--分隔符号

）

returnsint

as

begin

declare@locationint

declare@startint

declare@lengthint

set@str=ltrim（rtrim（@str））

set@location=charindex（@split,@str）

set@length=1

while@location<>0

begin

set@start=@location1

set@location=charindex（@split,@str,@start）

set@length=@length1

end

return@length

end

GO

CREATEfunctionGet_StrArrayStrOfIndex

（

@strvarchar（1024）,--要分割的字符串

@splitvarchar（10）,--分隔符号

@indexint--取第几个元素

）

returnsvarchar（1024）

as

begin

declare@locationint

declare@startint

declare@nextint

declare@seedint

set@str=ltrim（rtrim（@str））

set@start=1

set@next=1

set@seed=len（@split）

set@location=charindex（@split,@str）

while@location<>0and@index>@next

begin

set@start=@location@seed

set@location=charindex（@split,@str,@start）

set@next=@next1

end

if@location=0select@location=len（@str）1

--这儿存在两种情况：

1、字符串不存在分隔符号2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。

returnsubstring（@str,@start,@location-@start）

end

GO

CREATEPROCEDUREproc_Common_SuperLike

--要查询的表的主键字段名称

@primaryKeyNamevarchar（999）,

--要查询的表名

@talbeNamevarchar（999）,

--要查询的表的字段名称，即内容所在的字段

@contentFieldNamevarchar（999）,

--查询记录的个数（TOP*）,匹配的个数越多,排名越靠前

@selectNumbervarchar（999）,

--匹配字符分隔标记

@splitStringvarchar（999）,

--匹配字符组合字符串

@wordsvarchar（999）

AS

declare@sqlFirstvarchar（999）

declare@sqlCentervarchar（999）

declare@sqlLastvarchar（999）

declare@nextint

declare@arrayLengthint

declare@newTablevarchar（999）

BEGIN

set@newTable=''

set@sqlCenter=''

set@next=1

set@arrayLength=dbo.Get_StrArrayLength（@words,@splitString）

while@next<=@arrayLength

begin

--构造sql查询条件（中间部分）

--判断是否是必要词

ifCHARINDEX（'[',dbo.Get_StrArrayStrOfIndex（@words,@splitString,@next））>0

begin

set@sqlCenter=@sqlCenter'SELECT'@primaryKeyName','CONVERT（varchar（999）,@arrayLength-@next1）'ASwordPowerFROM'@talbeName'WHERECONTAINS（'@contentFieldName',''"*'REPLACE（REPLACE（dbo.Get_StrArrayStrOfIndex（@words,@splitString,@next）,'[',''）,']',''）'*"''）UNIONALL'

--构造必要词

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-@next1）'ASwordPowerFROM'@talbeName'WHERECONTAINS（'@contentFieldName',''"*'dbo.Get_StrArrayStrOfIndex（@words,@splitString,@next）'*"''）UNIONALL'

end

set@next=@next1

end

--判断是否有必要词

ifCHARINDEX（'[',@words）>0

begin

---处理必要词部分,去除最后无用语句

set@newTable=left（@newTable,（len（@newTable）-4））

set@newTable='ASt_TempWHERE'@primaryKeyName'IN（SELECT'@primaryKeyName'FROM'@talbeName'WHERE'@newTable'）'

end

else

begin

set@newTable='ASt_Temp'

end

--处理sql语句中间部分,去除最后无用语句

set@sqlCenter=left（@sqlCenter,（len（@sqlCenter）-10））

--构造sql语句开头部分

set@sqlFirst='SELECTTOP'@selectNumber''@primaryKeyName',COUNT（*）SUM（wordPower）ASfinalPowerFROM（'

--构造sql语句结尾部分

set@sqlLast='）'@newTable'GROUPBY'@primaryKeyName'ORDERBYfinalPowerDESC'

--拼接出完整sql语句,并执行

Execute（@sqlFirst@sqlCenter@sqlLast）

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

--开启全文索引

sp_fulltext_databaseenable

--创建索引目录（创建出来是一个目录，用来放索引文件）

CREATEFULLTEXTCATALOG索引目录名称--例如myFullText

--创建全文索引

CREATEFULLTEXTINDEXON表名（字段名）--为哪个表的哪个字段创建全文索引，例如t_test（content）

KEYINDEX主键索引名称ON索引目录名称--注意是主键索引名称，而不是主键字段名称！

例如，PK__t_test__3213E83F0EA330E9；指定全文索引目录，即放在哪个目录下，例如myFullText

注意：

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