导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一算法~

y51704545 2007-11-29 02:20:23
得到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的哪个串

分不多了~谢谢~
...全文
44 点赞 收藏 6
写回复
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
playwarcraft 2007-11-29

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
回复
-狙击手- 2007-11-29
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

*/
回复
y51704545 2007-11-29
需要得到相同的数字连成的串~
'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'
回复
wzy_love_sly 2007-11-29
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)

感觉很烂
回复
-狙击手- 2007-11-29
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
*/
回复
-狙击手- 2007-11-29
declare @s varchar(8000)
set @s = 5个串的用',' 相加起来
exec('selct COALESCE ('+@s+')')
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告