--为了效率,所以要一个辅助表配合
select top 8000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
gocreate function f_split(
@str varchar(8000), --要分拆的字符串
@splitchar varchar(10) --分隔符
)returns table
as
return(
select re=substring(@str,id,charindex(@splitchar,@str+@splitchar,id)-id)
from 序数表
where id<=len(@str)+1 and charindex(@splitchar,@splitchar+@str,id)-id=0
)
go
declare @s varchar(200)
select @s=class from 表A where [name]='00'select a.id,a.class,b.[name] from 表B a left join (select '00' as [name],* from f_split(@s,',')) b on a.class=b.re
select top 8000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
gocreate function f_split(
@str varchar(8000), --要分拆的字符串
@splitchar varchar(10) --分隔符
)returns table
as
return(
select re=substring(@str,id,charindex(@splitchar,@str+@splitchar,id)-id)
from 序数表
where id<=len(@str)+1 and charindex(@splitchar,@splitchar+@str,id)-id=0
)
go
declare @s varchar(200)
select @s=class from 表A where [name]='00'select a.id,a.class,b.[name] from 表B a left join (select '00' as [name],* from f_split(@s,',')) b on a.class=b.re
insert into @a
select 1 , '00', '1,2,3' union all
select 2 , '01', '2,3' union all
select 3 , '02', '3,4,5'
declare @b table (id int,class varchar(20))
insert into @b
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 select b.*,a.name
from @b b
left join (select * from @a where id=1) a
on charindex(','+b.class+',',','+a.class+',')>0
今晚结贴!