搜索存储过程 查询关键字匹配度的记录

具体分为三层 从最里面开始写

--搜索存储过程 查询关键字匹配度大于1的记录 
ALTER proc [dbo].[proc_perSearch]
@keywords varchar(100),
@PageSize int = 10, -- 页大小
@PageIndex int = 1, -- 页索引
@jp_post varchar(20)
as
    declare @sql nvarchar(max)
    declare @countsql nvarchar(max)
    declare @keylength int --关键词长度
    declare @matching int --匹配度指数
    set @keylength=len(@keywords)
    if(@keylength>5)--如果关键词长度大于5则查询匹配度为（长度-2个字符）
    begin
        set @matching=@keylength-2
    end
    else if(@keylength>=4) --如果关键词长度为4 则匹配3
    begin
        set @matching=@keylength-1
    end
    else if(@keylength<=3) --如果关键词长度为3 则匹配2
    begin
        set @matching=@keylength-1
    end
    else if(@keylength<=2) --如果关键词长度为2 则匹配2
    begin
        set @matching=@keylength
    end
    set @sql='select top '+str(@PageSize)+' * from(select top 100000 ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT v)DESC)as rowNumber,
    COUNT(DISTINCT v) matching,jp_post,jp_salary,jp_Education,jp_Workingyears,jp_address,cp_name from(
select  jp_post,jp_salary,jp_Education,jp_Workingyears,jp_address,cp_name from re_Jobposition r inner join re_company c  on r.cp_id = c.cp_id
) as a inner join fn_SplitStringToROWS(''+@keywords+'') b on CHARINDEX(b.v,a.jp_post)>0 
or CHARINDEX(b.v,a.jp_salary)>0 
or CHARINDEX(b.v,a.jp_Education)>0 
or CHARINDEX(b.v,a.jp_address)>0 
or CHARINDEX(b.v,a.cp_name)>0 
group by jp_post,jp_salary,jp_Education,jp_Workingyears,jp_address,cp_name 
order by count(DISTINCT v)DESC,NEWID()
) as Job where rowNumber between (('+@pageIndex+'-1)*'+@pageSize+'1) and '+@pageIndex+' * '+@pageSize+
   'and macthing>='+@matching+ 'and rs_offon = 0 and rs_city  like ''%'+@jp_post+'%''';