能否这样排序

[cloudgamer]

根据一个字段值是这样的
1 .a.b.c.d.e.
2 .a.b.c.
3 .a.
然后按匹配度排序
例如要匹配a b d
就按1 2 3排
要匹配b d
就按1 2排
有什么方法

[SQLCenter]

what?

[xmx2009]

帮顶，接分

[duanzhi1984]

嘛？？？？

[duanzhi1984]

请详细点

[pt1314917]

--这样？
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,col varchar(50))
insert into [tb]
select 1,'.a.b.c.d.e.' union all
select 2,'.a.b.c.' union all
select 3,'.a.'
go

--匹配 a b d
select * from
[tb]
order by ((case when charindex('.a.',col)>0 then 1 else 0 end)+(case when charindex('.b.',col)>0 then 1 else 0 end)+(case when charindex('.d.',col)>0 then 1 else 0 end))  desc

[hokor]

if object_id('tb') is not null
drop table tb
select 1 id,'.a.b.c.d.e.' string into tb
union all select 2 id,'.a.b.c.'
union all select 3 id,'.a.'

declare @a table(col varchar(30))
insert into @a
select 'a b d' --查找字符串赋值
;with findstr as(
SELECT  row_number()OVER(ORDER BY col)rn,col='.'+SUBSTRING(a.col,b.number,CHARINDEX(' ',a.col+' ',b.number)-b.number)+'.'
  FROM @a a,master..spt_values b
 WHERE b.number<=LEN(a.col)+1 
  AND CHARINDEX(' ',' '+a.col,b.number)=b.number
  AND b.type = 'P')

select id,string,sum(isfind) sort from (
select t.id,t.string,case when charindex(f.col,t.string,0)>=1 then 1 else 0 end isfind from findstr f , tb t)
as a
group by id,string 
order by sort desc

/* 'a b d'
id          string      sort
----------- ----------- -----------
1           .a.b.c.d.e. 3
2           .a.b.c.     2
3           .a.         1
*/

/* 'b d'
id          string      sort
----------- ----------- -----------
1           .a.b.c.d.e. 2
2           .a.b.c.     1
3           .a.         0
*/

[feixianxxx]

if object_id('[tb]') is not null 
drop table [tb]
create table [tb] (id int,col varchar(50))
insert into [tb]
select 1,'.a.b.c.d.e.' union all
select 2,'.a.b.c.' union all
select 3,'.a.'
GO
DECLARE @s VARCHAR(100)
SET @s='a b d'
SELECT k.id
FROM (
SELECT t.id,col=substring(stuff(t.col,1,1,''),s.number,charindex('.',stuff(t.col,1,1,''),s.number)-s.number)
FROM tb t JOIN master..spt_values s ON s.number BETWEEN 1 AND len(t.col)
WHERE s.type='p' AND substring(LEFT(t.col,len(t.col)-1),s.number,1)='.') k 
WHERE charindex(k.col,@s)>0
GROUP BY k.id
HAVING(count(*)>0)
ORDER BY count(*) DESC 
/*
id  
----
   1
   2
   3
*/