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

# 求一算法~

y51704545 2007-11-29 02:20:23

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

...全文
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'

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

3.2w+

MS-SQL Server相关内容讨论专区