模糊查询 按字数多少排序

declare @key nvarchar(20)set @key = '打印机 ibm a'declare @return nvarchar(100)declare @sql nvarchar(max)set @sql  = ''if charindex(' ',@key) > 0beginwhile charindex(' ',@key) > 0     begin        select @return = substring(@key,1,charindex(' ',@key) - 1)--print @returnset @sql = @sql +  'select * from (select *,len(search) s1, len(replace(search,'''+@return+''','''')) s2 from vfit) a where s1 != s2 union all '--print( @sql)select @key = ltrim(stuff(@key,1,charindex(' ',@key),''))        if charindex(' ',@key) <= 0        beginset @sql = @sql +  'select * from (select *,len(search) s1, len(replace(search,'''+@key+''','''')) s2 from vfit) a where s1 != s2'            break        end    end-- 上面循环是拼接需要union 的总数据-- 下面是是分组并排序，按倒序排列set @sql = 'select * from tfit inner join (select fitid ,sum(s1)-sum(s2) as numb from (' + @sql + ') b group by fitid) c on tfit.fitid = c.fitid order by c.numb desc'--print( @sql)exec(@sql)endelsebegin;with c2 as(select *,len(search) s1, len(replace(search,@key,'')) s2 from vfit )select * from c2 where s1 != s2 order by s1-s2 descend

这种写法不考虑效率