求一算法~

[y51704545]

得到5个ID的字符串如:

'1,2,3,4,5,6',
'3,4,5,6,7'
'4,7,6,8,9,10'
'4,5,6,7,8'
'4,6,8,9'

如何得到里面相同的字符串

如果 其中一个串的值为 null 则排除此串,在其余的4个串中选择,如果其中4个均为null 则 返回不为null的哪个串

分不多了~谢谢~

[-狙击手-]

declare @s varchar(8000)
set @s = 5个串的用',' 相加起来
exec('selct COALESCE ('+@s+')')

[-狙击手-]

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
*/

[wzy_love_sly]

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)

感觉很烂

[y51704545]

需要得到相同的数字连成的串~
'1,2,3,4,5,6',
'3,4,5,6,7'
'4,7,6,8,9,10'
'4,5,6,7,8'
'4,6,8,9'

里相同的是:'4,6'

[-狙击手-]

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

*/

[playwarcraft]

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