在论坛中出现的比较难的sql问题：12(递归问题2 拆分字符串)

所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。

请教一个MSSQLSELECT语名的写法 

http://bbs.csdn.net/topics/390649166?page=1#post-396149924

表结构
id  ids
1   3,8,83,92,215,7

传入一个值8,92要取出3,83,215,7
即是取出不存在于传入ID串其它所有ID都取出来.
请问这个SELECT 语语应该怎么写。

递归replace，我的解法：

drop table t
go
 
create table t(id int,  ids varchar(100))
 
insert into t
select 1 ,  ‘3,8,83,92,215,7‘
go
 
 
 
declare @a varchar(100) = ‘8,7‘
 
;with tt
as
(
select id, ids,@a+‘,‘ as a,ids+‘,‘ as ids_t
from t
where ids like (‘%‘ + REPLACE(@a,‘,‘, ‘%‘) + ‘%‘)
),
 
ttt
as
(
select id,ids,
       cast(a as varchar(max)) as a,
       cast(ids_t as varchar(max)) as ids_t ,
       1  as level
from tt
 
union all
 
select id,ids,
       cast(stuff(a,1,charindex(‘,‘,a),‘‘) as varchar(max)) ,
       cast(replace(ids_t,left(a,charindex(‘,‘,a)),‘‘) as varchar(max)),
       level + 1
from ttt
where charindex(‘,‘,a) > 0
 
)
 
 
select id, ids_t
from 
(
select id, ids,left(ids_t,len(ids_t)-1) as ids_t,
       ROW_NUMBER() over(partition by id order by level desc) as rownum 
from ttt 
)a
where rownum = 1
/*
id	ids_t
1	3,83,92,215
*/