SQL SERVER 2005 中,对两集合中有多少相同元素的检测

总是有C友在网上问这样的问题：有列col1，nvarchar型，值可能为 'abc,erfoi,dfij,dja'，有列col2，同型，值可能为'张三,李四,王二麻子'，问这两列中有多少“元素”相同。

首先，这种数据表设计真的有待改进，因为关系型数据库中，一个列，应该是一种属性，而不应该成为一个集合，如果成为一个集合，就应该拆成一个新表，将它与原表相连接就行了，何必做成这样麻烦的处理方式呢！

不过，既有需求，就要有应对的办法，何况我在实际设计的时候也有过类似的处理（某个课程，由几个教师同时上，教师名间以逗号分隔。。。先BS一下自己！）。

建了一个函数，用于在拆分集合后进行比较：

create function splitsame(@col1 nvarchar(100),@col2 nvarchar(100))
returns int
as
begin
declare @t1 table(col varchar(10))
declare @t2 table(col varchar(10))
declare @i int

insert into @t1
  select substring(@col1,number,charindex(',',@col1+',',number+1)-number) from master..spt_values
    where type='p' and number<=len(@col1) and substring(@col1,number,1)<>',' and substring(','+@col1,number,1)=','
insert into @t2
  select substring(@col2,number,charindex(',',@col2+',',number+1)-number) from master..spt_values
    where type='p' and number<=len(@col2) and substring(@col2,number,1)<>',' and substring(','+@col2,number,1)=','
select @i=count(*) from @t1 a,@t2 b where a.col=b.col

return @i
end
go
select dbo.splitsame('fsi,as,fwe,we','vew,as,rrc,fwe,dif2w')
/*

-----------
2

(1 行受影响)

*/

如果要求某表同一行中两个列（集合）有多少相同“元素”时，直接调用函数就行了：

select  id,dbo.splitsame(col1,col2) from tb

其实，函数中的拆分也可以用XML查询来做，或许比这样做法更简单些。