请教一个MSSQLSELECT语名的写法
表结构
id ids
1 3,8,83,92,215,7

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

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
/*
idids_t
13,83,92,215
*/

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2013-11-21 23:19:12
-- Version:
--      Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) 
--Sep 22 2011 00:28:06 
--Copyright (c) 1988-2008 Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[ids] varchar(15))
insert [tb]
select 1,'3,8,83,92,215,7'
--------------开始查询--------------------------
Select
    a.id,ids=substring(a.ids,b.number,charindex(',',a.ids+',',b.number)-b.number)
into #tb 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.ids)
where
     substring(','+a.ids,b.number,1)=','
     
declare @str varchar(8000) 

set @str = '8,105,7'

set @str =  'select  ids='''+replace(@str,',',''''+' union all select ''')+'''' 


set @str='select ids into #temp from ('+@str+') a  select * from #temp a where not exists(select 1 from #tb where ids=a.ids) drop table #tb,#temp'

exec(@str)
----------------结果----------------------------
/* ids
----
105

(1 行受影响)

*/

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,105,7'


;with tt
as
(
select 
       ids,
       SUBSTRING(t.ids, number ,CHARINDEX(',',t.ids+',',number)-number) as v
from (select @a as ids)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.ids,s.number,1) = ','
)

select tt.ids,tt.v 
 
from tt
where not exists(select 1 from t 
                 where CHARINDEX(','+tt.v+',',','+t.ids+',') > 0) 
/*
ids    v
8,105,7105
*/