歌曲表:
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如何得到 歌曲IDS: 1,2,3 (演奏者演奏过的歌曲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如何得到 歌曲IDS: 1,2,3 (演奏者演奏过的歌曲ID串)没多少分了~谢谢~
set @IDS = '4,5'
select *
from table
where charindex(@IDS+',' ,演奏者IDS) > 0
where charindex(','+'4,5'+',',','+演奏者IDS+',')>0
select * from t
where charindex(','+'4,5'+',',演奏者IDS)>0
呵呵
create table #
(ID int, Name varchar(10), 演奏者IDS varchar(100) )
insert into #
select 1, 'aaa', ',1,2,3,4,5,' union all
select 2, 'bbb', ',3,4,5,6,7,' union all
select 3, 'ccc', ',7,5,9,' union all
select 4, 'ddd', ',3,' declare @IDS varchar(10)
set @IDS = '4,5'
select *
from #
where charindex(','+@IDS+',' ,演奏者IDS) > 0/*
ID Name 演奏者IDS
----------- ---------- ----------------------------------------------------------------------------------------------------
1 aaa ,1,2,3,4,5,
2 bbb ,3,4,5,6,7,(所影响的行数为 2 行)
*/
insert @t select 1,'aaa',',1,2,3,4,5,'
insert @t select 2,'bbb',',3,4,5,6,7,'
insert @t select 3,'ccc',',7,5,9,'
insert @t select 4,'ddd',',3,'declare @IDS varchar(10)
set @IDS = '4,5'
select *
from @t
where charindex(@IDS+',' ,演奏者IDS) > 0
/*ID Name 演奏者IDS
----------- ---- ------------------
1 aaa ,1,2,3,4,5,
2 bbb ,3,4,5,6,7,*/
呵呵----------------------------见笑
declare @tb table(ID int, [Name] nvarchar(20), [演奏者IDS] nvarchar(50))
insert into @tb
select 1, 'aaa', ',1,2,3,4,5,'
union all select 2, 'bbb', ',3,4,5,6,7,'
union all select 3, 'ccc', ',7,5,9,'
union all select 4, 'ddd', ',3,' declare @IDS varchar(10)
set @IDS = '4,5'
select *
from @tb
where [演奏者IDS] like '%'+@IDS+'%'ID Name 演奏者IDS
----------- -------------------- --------------------------------------------------
1 aaa ,1,2,3,4,5,
2 bbb ,3,4,5,6,7,(2 row(s) affected)
所以也需要得到 ID 为3的歌曲~或许传入 的IDS不是'4,5',而是'5,4'呢~也就是说~只要演奏者IDS 里只要有 4或者5 就取出来~而这个 变量 IDS 是不定的~谢谢~
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)>01 aaa ,1,2,3,4,5,
2 bbb ,3,4,5,6,7,
3 ccc ,,7,5,9,
declare @tb table(ID int, [Name] nvarchar(20), [演奏者IDS] nvarchar(50))
insert into @tb
select 1, 'aaa', ',1,2,3,4,5,'
union all select 2, 'bbb', ',3,4,5,6,7,'
union all select 3, 'ccc', ',7,5,9,'
union all select 4, 'ddd', ',3,' declare @IDS varchar(10), @IDS1 NVARCHAR(10), @IDS2 NVARCHAR(10)
set @IDS = '4,5'
SET IDS1 =substring(@IDS,0,charindex(@IDS,','))
SET IDS2 =substring(@IDS,charindex(@IDS,','),len(@IDS))
select *
from @tb
where [演奏者IDS] like '%'+@IDS1+'%' and [演奏者IDS] like '%'+@IDS2+'%'ID Name 演奏者IDS
----------- -------------------- --------------------------------------------------
1 aaa ,1,2,3,4,5,
2 bbb ,3,4,5,6,7,(2 row(s) affected)
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 * from @a where patindex('%,'+'4,5+',%','+ids+',')>0
declare @tb table(ID int, [Name] nvarchar(20), [演奏者IDS] nvarchar(50))
insert into @tb
select 1, 'aaa', ',1,2,3,4,5,'
union all select 2, 'bbb', ',3,5,4,6,7,'
union all select 3, 'ccc', ',7,5,9,'
union all select 4, 'ddd', ',3,4' declare @IDS varchar(10), @IDS1 NVARCHAR(10), @IDS2 NVARCHAR(10)
set @IDS = '4,5'
SET @IDS1 =(case when substring(@IDS,0,charindex(',',@IDS))<>'' then substring(@IDS,0,charindex(',',@IDS)) else @IDS end)
SET @IDS2 =(case when substring(@IDS,charindex(',',@IDS)+1,len(@IDS))<>'' then substring(@IDS,charindex(',',@IDS)+1,len(@IDS)) else @IDS end)
select *
from @tb
where [演奏者IDS] like '%'+@IDS1+'%' and [演奏者IDS] like '%'+@IDS2+'%'/*
(4 row(s) affected)
ID Name 演奏者IDS
----------- -------------------- --------------------------------------------------
1 aaa ,1,2,3,4,5,
2 bbb ,3,5,4,6,7,(2 row(s) affected)
*/
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串) 需要得到 的是: 1,2,3歌曲ID我的意思是如果传入的演奏者IDS字符串 如果是:4,9,5还是得到歌曲ID :1,2,3也就是说 演奏者IDS 里面只要有传入的字符串里任何一个数字就可以了~而非全部~和顺序没有关系的~
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
这个结果是对的但是:如果传进来的IDS是更常的:2,4,6,7,8,9,10如何呢~
create table abc (id int,name varchar(10),ids varchar(20))
insert abc select 1,'aaa',',1,2,3,4,5,'
insert abc select 2,'bbb',',3,4,5,6,7,'
insert abc select 3,'ccc',',7,5,9,'
insert abc select 4,'ddd',',3,' declare @sql varchar(8000)
declare @abca varchar(100)
set @abca='2,4,6,7,8,9,10'
set @sql=''
while charindex(',',@abca)>0
begin
set @sql=@sql+' union all select '+left(@abca,charindex(',',@abca) - 1) +' as ids '
set @abca=right(@abca,len(@abca)- charindex(',',@abca) )
end
set @sql='select distinct a.* from abc a ,('+right(@sql,len(@sql)-10)+') t where charindex(cast(t.ids as varchar),a.ids)> 0 '
exec( @sql)