歌曲表:
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就可以了~谢谢 没多少分了~可以在我发的另一贴里给分~
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就可以了~谢谢 没多少分了~可以在我发的另一贴里给分~
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)----------------------------------1set @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
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'
set @s='5,4'
select * from T where
patindex('%,'+@s+',%',演奏者IDS)>0 or patindex('%,'+reverse(@s)+',%',演奏者IDS)>0
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)这种
难道还不符合你的要求?
逸
等 级:
发表于:2007-11-28 23:42:384楼 得分:0
roy_88 太拘束于题目了吧,实际开发中我会在脚本中用数组比较,方便一些
------------------
要根据现实环境,不是新开发的系统?