declare @mystr varchar(500) declare @k varchar(500) declare @T varchar(1000)set @k='a,b,c,cc,dd,ac,ag,d5,ds45' set @mystr='cc,dd,ag,ds45'set @T=@k+',' + @mystr set @T='select B from (select ''' + REPLACE(@T,',',''' as B union all select ''') + ''' as ''B'' ) T group by B having count(1)=1' exec (@T)
declare @a table(k varchar(20)) insert @a select 'a' union all select 'b' union all select 'cc' union all select 'dd' union all select 'aa' union all select 'ag' union all select 'd5' union all select 'ds45'
declare @str varchar(20) set @str='a,b,c,ac,d5' select * from @a where k not in( select * from @a where charindex(k,@str)>0) /* (所影响的行数为 8 行)k -------------------- cc dd aa ag ds45(所影响的行数为 5 行)*/
建张表 ----- create table tb1 (id int identity(1,1),x varchar(1))insert into tb1 (x) select top 8000 '0'from sysobjects,syscolumns建个函数 ----- alter function f_test(@id varchar(8000),@sp varchar(10)) returns table as return ( select col=convert(varchar(100),substring(@id,id,charindex(@sp,@id+@sp,id)-id)) from tb1 where id<=len(@id) and charindex(@sp,@sp+@id,id)=id )查询 ----- declare @s varchar(100) select @s='' select @s=@s+','+col from dbo.f_test('a,b,c,cc,dd,ac,ag,d5,ds45',',') where col not in (select col from dbo.f_test('cc,dd,ag,ds45',',')) select @s=stuff(@s,1,1,'') select @s结果 ---- d5,b,ac,a,c
declare @k varchar(500)
declare @T varchar(1000)set @k='a,b,c,cc,dd,ac,ag,d5,ds45'
set @mystr='cc,dd,ag,ds45'set @T=@k+',' + @mystr
set @T='select B from (select ''' + REPLACE(@T,',',''' as B union all select ''') + ''' as ''B'' ) T group by B having count(1)=1'
exec (@T)
declare @a table(k varchar(20))
insert @a
select 'a'
union all
select 'b'
union all
select 'cc'
union all
select 'dd'
union all
select 'aa'
union all
select 'ag'
union all
select 'd5'
union all
select 'ds45'
declare @str varchar(20)
set @str='a,b,c,ac,d5'
select * from @a where k not in(
select * from @a where charindex(k,@str)>0)
/*
(所影响的行数为 8 行)k
--------------------
cc
dd
aa
ag
ds45(所影响的行数为 5 行)*/
-----
create table tb1 (id int identity(1,1),x varchar(1))insert into tb1 (x) select top 8000 '0'from sysobjects,syscolumns建个函数
-----
alter function f_test(@id varchar(8000),@sp varchar(10))
returns table
as return
(
select col=convert(varchar(100),substring(@id,id,charindex(@sp,@id+@sp,id)-id)) from tb1 where id<=len(@id) and charindex(@sp,@sp+@id,id)=id
)查询
-----
declare @s varchar(100)
select @s=''
select @s=@s+','+col from dbo.f_test('a,b,c,cc,dd,ac,ag,d5,ds45',',') where col not in (select col from dbo.f_test('cc,dd,ag,ds45',','))
select @s=stuff(@s,1,1,'')
select @s结果
----
d5,b,ac,a,c
"2,22,222"
"2,22"
这样的情况就不对了