sql 从一个数域中取出除其子集的其他数
sql 从一个数域中取出除其子集的其他数

简言之 想实现sql
select (1,2,3) not in (1,2)

select 后边怎么加一个数组，我只知道他能检索一行，
同理后边怎么在条件里表示一个数字域。
还有
多列转一行用stuff（）函数能做，但那变成字符串了，不好取数域差集了
还有什么方法吗



[解决办法]

SQL code

create function f(@s nvarchar(100),@sCoe nvarchar(100))returns varchar(100)asbegindeclare @tmp nvarchar(50),@iBegin int ,@iLen int     set @s='<'+replace(@s,',','><')+'>'    set @sCoe='<'+replace(@sCoe,',','><')+'>'    set @iBegin=1    set @iLen=len(@s)    while(@iBegin<@iLen)        begin            if left(@s,1)='<'            begin                set @tmp=substring(@s,charindex('<',@s,@iBegin),charindex('>',@s,@iBegin))                set @sCoe=replace(@sCoe,@tmp,'')                set @iBegin=charindex('>',@s,@iBegin)+1            end        end    return replace(replace(replace(@sCoe,'><',''),'>',''),'<','')endgocreate table ta (id int ,colle nvarchar(50))insert into taselect 1, '1,2'union allselect 2,'2,3'union allselect 3,'3,1'goselect id,dbo.f(colle ,'1,2,3') from tadrop table tadrop function f
[解决办法]
SQL code
--我的和你的一样的，都是用函数，只是稍微改了一点点create function f_split(@s     varchar(8000),  --待分拆的字符串@split varchar(10)     --数据分隔符)returns tableas return (  select Row_number()over(order by Number) rn ,  substring(@s,number,charindex(@split,@s+@split,number)-number)as col  from master..spt_values  where type='p' and number<=len(@s+'a')   and charindex(@split,@split+@s,number)=number  )goCREATE function ff(@sCoe nvarchar(100),@s nvarchar(100))returns varchar(100)asbegindeclare @St nvarchar(100)select @St = isnull(@St,'')+ col+',' from dbo.f_split(@s,',') a        where not exists (select * from dbo.f_split(@sCoe,',') where col = a.col)set @St = stuff(@St,len(@St),1,'')return @Stendgocreate table #ta (id int ,colle nvarchar(50))insert into #ta select 1, '1,2'union all select 2,'2,3'union all select 3,'3,1,4'goselect id,dbo.ff(colle ,'1,2,3,6,7,4') from #tadrop table #tadrop function f