22,300
社区成员




create table tb(s varchar(20))
insert into tb select '101,102,103,107'
insert into tb select '102,103,107'
insert into tb select '103,101,1077'
declare @sql varchar(8000)
set @sql='103,101,107'
set @sql='select * into ## from (select '+replace(@sql,',',' as id union all select ')+')tp'
exec(@sql)
declare @where varchar(8000)
select @where=isnull(@where+' and ','')+'charindex('','+ltrim(id)+','','',''+s+'','')>0'
from ##
exec('select * from tb where '+@where)
Create table tt(name varchar(20))
insert into tt select '101, 102, 103, 107'
Create function f_tt(@v varchar(20))
returns @tb table(name varchar(10))
as
begin
while charindex(',',@v)>0
begin
insert into @tb select substring(@v,1,charindex(',',@v)-1)
set @v=stuff(@v,1,charindex(',',@v),'')
end
insert into @tb select @v
return
end
declare @v varchar(20)
set @v='103,101,107'
select * from tt a,dbo.f_tt(@v) b
where charindex(','+b.name+',',','+a.name+',')>0
create table tb(s varchar(20))
insert into tb select '101,102,103,107'
insert into tb select '102,103,107'
declare @sql varchar(8000)
set @sql='103,101,107'
set @sql='select * into ## from (select '+replace(@sql,',',' as id union all select ')+')tp'
exec(@sql)
declare @where varchar(8000)
select @where=isnull(@where+' and ','')+'charindex('''+ltrim(id)+''',s)>0'
from ##
exec('select * from tb where '+@where)
DECLARE @para VARCHAR(1000),
@split VARCHAR(10)
SELECT @para = '117,118,123,',@split = ''
SELECT CHARINDEX(',',@para)
DECLARE @t TABLE(Id INT)
WHILE CHARINDEX(',',@para)>0
BEGIN
SELECT @split = LEFT(@para, CHARINDEX(',', @para)-1), @para = RIGHT(@para, LEN(@para)-CHARINDEX(',', @para))
INSERT INTO @t
SELECT @split
END
SELECT * FROM @t
--result
-----------
--4
--
--(1 row(s) affected)
--
--(1 row(s) affected)
--
--(1 row(s) affected)
--
--(1 row(s) affected)
--
--Id
-------------
--117
--118
--123
--
--(3 row(s) affected)
--最后你在用这个@t与你的主表做连接查询。
--具体你自己在改改