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

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

y51704545 2007-11-28 05:04:57

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,

...全文
124 点赞 收藏 6

6 条回复

stzjzs

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

------------------

wzy_love_sly 2007-11-28
``````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``````

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

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

pt1314917 2007-11-28
``````
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'
``````

-狙击手- 2007-11-28
``````

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

MS-SQL Server

3.2w+

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