declare @key nvarchar(20)
set @key = '打印机 ibm a'
declare @return nvarchar(100)
declare @sql nvarchar(max)
set @sql = ''
if charindex(' ',@key) > 0
begin
while charindex(' ',@key) > 0
begin
select @return = substring(@key,1,charindex(' ',@key) - 1)
--print @return
set @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
begin
set @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)
end
else
begin
;with c2 as
(
select *,len(search) s1, len(replace(search,@key,'')) s2 from vfit
)
select * from c2 where s1 != s2 order by s1-s2 desc
end
这种写法不考虑效率