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

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

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


[解决办法]

create function f(@s nvarchar(100),@sCoe nvarchar(100))
returns varchar(100)
as
begin
declare @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,'><',''),'>',''),'<','')
end
go
create 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'

go

select id,dbo.f(colle ,'1,2,3') from ta

drop table ta
drop function f

--我的和你的一样的，都是用函数，只是稍微改了一点点
create function f_split
(
@s     varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)returns table
as
 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
  )
go

CREATE function ff(@sCoe nvarchar(100),@s nvarchar(100))
returns varchar(100)
as
begin
declare @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 @St
end
go

create 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'
go

select id,dbo.ff(colle ,'1,2,3,6,7,4') from #ta

drop table #ta
drop function f