declare @s varchar(100),@sql varchar(1000) set @s = '20,50' set @sql = replace(@s,',',''' as id union all select ''') set @sql = 'select '''+@sql + ''''select @sql = 'select * from ta a,('+@sql+') b where a.SSItemID = b.id and charindex('',''+id+'','','',''+SSItemID+'','') >0' exec( @sql)
--> Test Data: [tabel1] if object_id('[tabel1]') is not null drop table [tabel1] create table [tabel1] ([SSItemID] varchar(50)) insert into [tabel1] select '1,20,33,11,23' go --select * from [tabel1] --Code declare @s varchar(50) declare @sql varchar(2000) begin create table #tmp(num varchar(10)) set @s='20,55' select @sql='select '+replace(@s,',',' union all select ') insert #tmp exec(@sql) exec(@sql) select distinct a.* from [tabel1] a,#tmp b where charindex(','+b.num+',',','+a.SSItemID+',')>0drop table #tmp end --Drop drop table tabel1 --Result /* SSItemID -------------------------------------------------- 1,20,33,11,23 */
你的数据是 1,20,33,11,23 用 select SSItemID from xx where charindex('20,55',','+rtrim(SSItemID)+',') >0 怎么能查出来? select SSItemID from xx where charindex('20,33',','+rtrim(SSItemID)+',') >0 应该。。
set @s = '20,50'
set @sql = replace(@s,',',''' as id union all select ''')
set @sql = 'select '''+@sql + ''''select @sql = 'select * from ta a,('+@sql+') b where a.SSItemID = b.id and charindex('',''+id+'','','',''+SSItemID+'','') >0'
exec( @sql)
if object_id('[tabel1]') is not null drop table [tabel1]
create table [tabel1] ([SSItemID] varchar(50))
insert into [tabel1]
select '1,20,33,11,23'
go
--select * from [tabel1]
--Code
declare @s varchar(50)
declare @sql varchar(2000)
begin
create table #tmp(num varchar(10))
set @s='20,55'
select @sql='select '+replace(@s,',',' union all select ')
insert #tmp exec(@sql)
exec(@sql)
select distinct a.* from [tabel1] a,#tmp b where charindex(','+b.num+',',','+a.SSItemID+',')>0drop table #tmp
end
--Drop
drop table tabel1
--Result
/*
SSItemID
--------------------------------------------------
1,20,33,11,23
*/
用
select SSItemID from xx where charindex('20,55',','+rtrim(SSItemID)+',') >0 怎么能查出来?
select SSItemID from xx where charindex('20,33',','+rtrim(SSItemID)+',') >0 应该。。