求一查询语句~或小算法~

[y51704545]

歌曲表:
ID Name 演奏者IDS
1 aaa ,1,2,3,4,5,
2 bbb ,3,4,5,6,7,
3 ccc ,7,5,9,
4 ddd ,3,

另得到一演奏者IDS字符串：‘4,5’ 或者‘5，4’

均要得到得到 歌曲IDS: 1,2,3 (演奏者演奏过的歌曲ID串) 因为 歌曲的 演奏者IDS 里有演奏着IDS里的任意一个ID就可以了~

谢谢 没多少分了~

可以在我发的另一贴里给分~

[-狙击手-]

create table tb(ID int,[Name] char(3),演奏者IDS varchar(20))
insert tb select 1,'aaa',',1,2,3,4,5,'   
insert tb select 2,'bbb',',3,4,5,6,7,'   
insert tb select 3,'ccc',' ,7,5,9,'   
insert tb select 4,'ddd',',3,'   

declare @s varchar(8000)

----------------------------------1

set @s = '5,4'
set @s = REPLACE (@s ,',', ','+''' charindex(  '''+',')

set @s = REPLACE (@s ,' charindex(  ', ' ,演奏者IDS) > 0 and  charindex( ')


set @s = 'charindex(  '','+@s+ ','',演奏者IDS) > 0'

exec ('select * from tb where '+ @s)

/*

ID          Name 演奏者IDS               
----------- ---- -------------------- 
1           aaa  ,1,2,3,4,5,
2           bbb  ,3,4,5,6,7,
*/


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


set @s = '4,5'
set @s = REPLACE (@s ,',', ','+''' charindex(  '''+',')

set @s = REPLACE (@s ,' charindex(  ', ' ,演奏者IDS) > 0 and  charindex( ')


set @s = 'charindex(  '','+@s+ ','',演奏者IDS) > 0'

exec ('select * from tb where '+ @s)


/*

ID          Name 演奏者IDS               
----------- ---- -------------------- 
1           aaa  ,1,2,3,4,5,
2           bbb  ,3,4,5,6,7,

*/

drop table tb

[pt1314917]

create proc wsp
@IDS varchar(100)
as
declare @sql varchar(1000)
set @sql='charindex('''
select @sql=@sql+replace(''+@IDS+'',',',''',演奏者IDS)>0 or charindex(''')+''',演奏者IDS)>0'
exec('select * from tb where '+@sql)

exec wsp '4,5'

[中国风]

declare @s nvarchar(50)
set @s='5,4'
select * from T where
patindex('%,'+@s+',%',演奏者IDS)>0 or patindex('%,'+reverse(@s)+',%',演奏者IDS)>0

[stzjzs]

roy_88 太拘束于题目了吧，实际开发中我会在脚本中用数组比较，方便一些

[wzy_love_sly]

declare @a table (id int,name varchar(10),ids varchar(20))
insert @a select 1,'aaa',',1,2,3,4,5,'
insert @a select 2,'bbb',',3,4,5,6,7,'
insert @a select 3,'ccc',',,7,5,9,'
insert @a select 4,'ddd',',3,'


select distinct a.* from @a a ,(select 4 as ids union select 5 ids)t 
where charindex(cast(t.ids as varchar),a.ids)>0

把‘5，3，2，6’用reaplace替换成（select 4 as ids union select 5 ids）这种
难道还不符合你的要求？

[中国风]

stzjzs
逸
等 级：
发表于：2007-11-28 23:42:384楼 得分:0
roy_88 太拘束于题目了吧，实际开发中我会在脚本中用数组比较，方便一些

------------------
要根据现实环境，不是新开发的系统？