select aa.id,aa.cname,sum(power) as power
from (select a.*,b.idd as power
from sorts a,#t b
where charindex(left(@str,b.idd),a.cname)>0
or charindex(right(@str,b.idd),a.cname)>0
) aa
group by aa.id,aa.cname
order by power desc
... order by case when charindex('电脑包装',cname) then 0 else 1 end,
case when charindex('电脑包',cname) then 0 else 1 end,
case when charindex('电脑',cname) then 0 else 1 end,
case when charindex('包装',cname) then 0 else 1 end,
case when charindex('电',cname) then 0 else 1 end
set @str='电脑包装'
set @len=len(@str)
set rowcount @len
select idd=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0
select aa.id,aa.cname,sum(power) as power
from (select a.*,b.idd as power
from sorts a,#t b
where charindex(left(@str,b.id),a.cname)>0 or charindex(right(@str,b.id),a.cname)>0) aa
group by aa.id,aa.cname
order by power desc
set @str='电脑包装'
set @len=len(@str)
set rowcount @len
select idd=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0
select aa.id,aa.cname,sum(power) as power
from (select a.*,b.idd as power
from sorts a,#t b
where (charindex(left(@str,b.id),a.cname)>0 or charindex(right(@str,b.id),a.cname)>0) aa
group by aa.id,aa.cname
order by power desc
set @str='电脑包装'
set @len=len(@str)
set rowcount @len
select idd=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0
select aa.id,aa.cname,sum(power) as power
from (select a.*,b.idd as power
from 表 a,#t b
where charindex(substring(@str,1,b.idd),a.cname)>0 ) aa
group by aa.id,aa.cname
order by power desc
你试试 我没有测试
declare @len int
declare @str varchar(100)
set @str='电脑包装'
set @len=len(@str)
set rowcount @len
select idd=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0
select aa.id,aa.cname,sum(power) as power
from (select a.*,b.id as power
from 表 a,#t b
where charindex(substring(@str,1,b.id),a.cname)>0 ) aa
group by aa.id,aa.cname
order by power desc
USE pubs
GO
-- Returns a DIFFERENCE value of 4, the least possible difference.
SELECT SOUNDEX('Green'),
SOUNDEX('Greene'), DIFFERENCE('Green','Greene')
GO
-- Returns a DIFFERENCE value of 0, the highest possible difference.
SELECT SOUNDEX('Blotchet-Halls'),
SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene')
GO