求一算法~

create table T(col varchar(50))

insert into T select '1,2,3,4,5,6'

insert into T select '3,4,5,6,7'

insert into T select '4,7,6,8,9,10'

insert into T select '4,5,6,7,8'

insert into T select '4,6,8,9'



select top 100 id=identity(int,1,1)

into # 

from syscolumns a,syscolumns b



select id=identity(int,1,1) ,* into #t from T



declare @n int ,@str varchar(50)

select @n=count(*),@str=''  from T where col is not null



select @str=@str+','+col

from

(

select col,count(distinct id) as num

from

(

select  a.id,

          col=substring(col,b.id,charindex(',',col+',',b.id)-b.id)     from   #t   a,#   b 

where   substring(','+a.col,b.id,1)=','

) X1

group by col

having count(distinct id)=@n

)  X2



if len(@str)>1

 set @str=stuff(@str,1,1,'')



select @str

/*

4,6



*/





drop table T,#,#t

set nocount on

declare @t table(col varchar(50))

insert @t select '1,2,3,4,5,6'

union select '3,4,5,6,7'

union select '4,7,6,8,9,10' 

union select '4,5,6,7,8'  

union select '4,6,8,9' 





declare @tt table (id int)

declare @rowcount int

declare @rowcount1 int

declare @I int

set @I = 1

select @rowcount = count(1) from @t



while @I < 100 and @rowcount > 0

begin

	select @rowcount1 =count(1) from @t where charindex(','+ltrim(@i)+',',','+col+',') > 0 

    if @rowcount1 = @rowcount

       insert @tt select @I

    set @I = @I + 1

end 









declare @s varchar(8000)

set @s = ''

select @s = @s+','+ltrim(id) 

from @tt



select @s = right(@s,len(@s) - 1)



select @s



/*



                                                                                                                                                                                                                                                                 

-----

4,6



*/

declare @s1 varchar(8000)

set @s1='3,4,5,6,7' 

declare @s2 varchar(8000)

set @s2='4,7,6,8,9,10' 

declare @str1 varchar(8000)

declare @str2 varchar(8000)

declare @str3 varchar(8000)



set @str1='select '+replace (@s1,',',' as a union all select ')

set @str1='(select * from ('+@str1+' as a)a) a'

set @str2='select '+replace (@s2,',',' as a union all select ')

set @str2='(select * from ('+@str2+' as a)b) b'



set @str3='select * from '+@str1+','+@str2+' where a.a=b.a'

print @str3

exec (@str3)

declare @t table(col varchar(10))

insert @t select null union select 'asdfasf' union select 'asdfasf' union select 'wfwf' union select 'qefs'



select * from @t

declare @s varchar(8000)



set @s = ''

select @s = @s + case when col is null then 'null ' else ''''+col+'''' end+',' 

from @t 

select @s = substring(@s,1,len(@s) - 1)

exec('select COALESCE('+@s+')')



/*



col        

---------- 

NULL

asdfasf

qefs

wfwf



--result        

------- 

asdfasf

*/