select ...
WHERE a.[memberID]=b.[personalID] AND ( CHARINDEX(',0302,' , ','+ ExpectArea +',')>0 or CHARINDEX(',04' , ','+ ExpectArea +'')>0 or CHARINDEX(',01' , ','+ ExpectArea +'')>0 or CHARINDEX(',05' , ','+ ExpectArea +'')>0 or CHARINDEX(',02' , ','+ ExpectArea +'')>0 OR ExpectArea='3700') and DATEPART(year,born) between 1953 and 1989 and SkillEdu between 2 and 6 and SkillExp>=3 AND ( [KeyHistory] like '%深圳%' or [KeyOutline] like '%深圳%' or [ResumeName] like '%深圳%' or [SkillTechang] like '%深圳%' or [SkillSayme] like '%深圳%' OR [KeyHistory] like '%总监%' or [KeyOutline] like '%总监%' or [ResumeName] like '%总监%' or [SkillTechang] like '%总监%' or [SkillSayme] like '%总监%')此SQL中[ExpectArea]是多项搜索,数据类型是数组形式。
关键字从表单传递过来时是“深圳,总监”,搜索时则需要按两个关键字进行搜索。结果是此SQL效率非常之低。请教有没有更好的方法。
WHERE a.[memberID]=b.[personalID] AND ( CHARINDEX(',0302,' , ','+ ExpectArea +',')>0 or CHARINDEX(',04' , ','+ ExpectArea +'')>0 or CHARINDEX(',01' , ','+ ExpectArea +'')>0 or CHARINDEX(',05' , ','+ ExpectArea +'')>0 or CHARINDEX(',02' , ','+ ExpectArea +'')>0 OR ExpectArea='3700') and DATEPART(year,born) between 1953 and 1989 and SkillEdu between 2 and 6 and SkillExp>=3 AND ( [KeyHistory] like '%深圳%' or [KeyOutline] like '%深圳%' or [ResumeName] like '%深圳%' or [SkillTechang] like '%深圳%' or [SkillSayme] like '%深圳%' OR [KeyHistory] like '%总监%' or [KeyOutline] like '%总监%' or [ResumeName] like '%总监%' or [SkillTechang] like '%总监%' or [SkillSayme] like '%总监%')此SQL中[ExpectArea]是多项搜索,数据类型是数组形式。
关键字从表单传递过来时是“深圳,总监”,搜索时则需要按两个关键字进行搜索。结果是此SQL效率非常之低。请教有没有更好的方法。
( [KeyHistory] like '%深圳%' or [KeyOutline] like '%深圳%' or [ResumeName] like '%深圳%' or [SkillTechang] like '%深圳%' or [SkillSayme] like '%深圳%' OR [KeyHistory] like '%总监%' or [KeyOutline] like '%总监%' or [ResumeName] like '%总监%' or [SkillTechang] like '%总监%' or [SkillSayme] like '%总监%')改成
charindex('深圳',[KeyHistory]+[KeyOutline]+[ResumeName]+[SkillTechang])>0
or charindex('总监',[KeyHistory]+[KeyOutline]+[ResumeName]+[SkillTechang])>0试试~
set @s='深圳,总监'
select @s='COL LIKE ''%'+REPLACE(@s,',','%'' or col like ''%')+'%'''select @s
COL LIKE '%深圳%' or col like '%总监%'
[KeyHistory]+[KeyOutline]+[ResumeName]+[SkillTechang] like '%深圳%' or
[KeyHistory]+[KeyOutline]+[ResumeName]+[SkillTechang] like '%总监%'
column_A : [KeyHistory]+','+[KeyOutline]+','+[ResumeName]+','+[SkillTechang]