if object_id('ta') is not null drop table ta go if object_id('tb') is not null drop table tb gocreate table ta(id int,tage varchar(50),content varchar(50)) insert into ta select 1,'黑色,红色,蓝色','内容介绍一' insert into ta select 2,'白色,桔红','内容介绍二'create table tb(id int,tage varchar(50),content varchar(50)) insert into tb select 1,'黑色,黄色,粉红','内容介绍一' insert into tb select 2,'暗红,桔黄','内容介绍二'select * from tb t where exists( select 1 from ( select a.content,b.tage from (select content,tage=convert(xml,'<root><v>'+replace(tage,',','</v><v>')+'</v></root>') from ta)a outer apply(select tage=C.v.value('.','nvarchar(100)') from a.tage.nodes('/root/v')C(v))b) tp where content=t.content and charindex(','+tage+',',','+t.tage+',')>0 )id tage content 1 黑色,黄色,粉红 内容介绍一
if object_id('ta') is not null drop table ta go if object_id('tb') is not null drop table tb gocreate table ta(id int,tage varchar(50),content varchar(50)) insert into ta select 1,'黑色,红色,蓝色','内容介绍一' insert into ta select 2,'白色,桔红','内容介绍二'create table tb(id int,tage varchar(50),content varchar(50)) insert into tb select 1,'黑色,黄色,粉红','内容介绍一' insert into tb select 2,'暗红,桔黄','内容介绍二'select top 1000 id=identity(int,1,1) into # from sysobjects a,sysobjects b,sysobjects cselect * from tb t where exists( select 1 from ( SELECT content,RIGHT(stuff(tage+',',b.id,len(tage),''),charindex(',',reverse(stuff(','+tage+',',b.id,len(tage),'')))) as 'tage' FROM ta a INNER JOIN # b ON SUBSTRING(tage+',',b.id,1)=',') tp where content=t.content and charindex(','+tage+',',','+t.tage+',')>0 )id tage content 1 黑色,黄色,粉红 内容介绍一2000临时表
--创建环境create table a(ID int,Tage varchar(50),Content varchar(50)) insert into a select 1,'黑色,红色,蓝色','内容介绍一' union all select 2,'白色,桔红','内容介绍二' create table b(ID int,Tage varchar(50),Content varchar(50)) insert into b select 1,'黑色,黄色,粉红','内容介绍一' union all select 2,'暗红,桔黄','内容介绍二' declare @sql varchar(8000) select @sql='charindex('','+replace(tage,',',','','',''+tage+'','')>0 or charindex('',')+','','',''+tage+'','')>0' from a where id=1 set @sql='select * from b where '+@sql exec(@sql)--删除表 drop table a,b
改成like应该好理解些! select 'b.tage like ''%'+replace(tage,',','%'' or b.tage like ''%')+'%''' from ta
if object_id('ta') is not null drop table ta go if object_id('tb') is not null drop table tb gocreate table ta(id int,tage varchar(50),content varchar(50)) insert into ta select 1,'黑色,红色,蓝色','内容介绍一' insert into ta select 2,'白色,桔红','内容介绍二'create table tb(id int,tage varchar(50),content varchar(50)) insert into tb select 1,'黑色,黄色,粉红','内容介绍一' insert into tb select 2,'暗红,桔黄','内容介绍二'declare @sql varchar(1000) select @sql='b.tage like ''%'+replace(tage,',','%'' or b.tage like ''%')+'%''' from ta where id=1 set @sql='select * from tb b where '+@sql exec (@sql)1 黑色,黄色,粉红 内容介绍一lz好像没有说要用content关联!
drop table ta
go
if object_id('tb') is not null
drop table tb
gocreate table ta(id int,tage varchar(50),content varchar(50))
insert into ta select 1,'黑色,红色,蓝色','内容介绍一'
insert into ta select 2,'白色,桔红','内容介绍二'create table tb(id int,tage varchar(50),content varchar(50))
insert into tb select 1,'黑色,黄色,粉红','内容介绍一'
insert into tb select 2,'暗红,桔黄','内容介绍二'select * from tb t where exists(
select 1 from (
select a.content,b.tage from
(select content,tage=convert(xml,'<root><v>'+replace(tage,',','</v><v>')+'</v></root>') from ta)a
outer apply(select tage=C.v.value('.','nvarchar(100)') from a.tage.nodes('/root/v')C(v))b) tp
where content=t.content and charindex(','+tage+',',','+t.tage+',')>0
)id tage content
1 黑色,黄色,粉红 内容介绍一
drop table ta
go
if object_id('tb') is not null
drop table tb
gocreate table ta(id int,tage varchar(50),content varchar(50))
insert into ta select 1,'黑色,红色,蓝色','内容介绍一'
insert into ta select 2,'白色,桔红','内容介绍二'create table tb(id int,tage varchar(50),content varchar(50))
insert into tb select 1,'黑色,黄色,粉红','内容介绍一'
insert into tb select 2,'暗红,桔黄','内容介绍二'select top 1000 id=identity(int,1,1) into # from sysobjects a,sysobjects b,sysobjects cselect * from tb t where exists(
select 1 from (
SELECT content,RIGHT(stuff(tage+',',b.id,len(tage),''),charindex(',',reverse(stuff(','+tage+',',b.id,len(tage),'')))) as 'tage'
FROM ta a
INNER JOIN # b
ON SUBSTRING(tage+',',b.id,1)=',') tp
where content=t.content and charindex(','+tage+',',','+t.tage+',')>0
)id tage content
1 黑色,黄色,粉红 内容介绍一2000临时表
--创建环境create table a(ID int,Tage varchar(50),Content varchar(50))
insert into a
select 1,'黑色,红色,蓝色','内容介绍一' union all
select 2,'白色,桔红','内容介绍二' create table b(ID int,Tage varchar(50),Content varchar(50))
insert into b
select 1,'黑色,黄色,粉红','内容介绍一' union all
select 2,'暗红,桔黄','内容介绍二'
declare @sql varchar(8000)
select @sql='charindex('','+replace(tage,',',','','',''+tage+'','')>0 or charindex('',')+','','',''+tage+'','')>0' from a where id=1
set @sql='select * from b where '+@sql
exec(@sql)--删除表
drop table a,b
select 'b.tage like ''%'+replace(tage,',','%'' or b.tage like ''%')+'%''' from ta
if object_id('ta') is not null
drop table ta
go
if object_id('tb') is not null
drop table tb
gocreate table ta(id int,tage varchar(50),content varchar(50))
insert into ta select 1,'黑色,红色,蓝色','内容介绍一'
insert into ta select 2,'白色,桔红','内容介绍二'create table tb(id int,tage varchar(50),content varchar(50))
insert into tb select 1,'黑色,黄色,粉红','内容介绍一'
insert into tb select 2,'暗红,桔黄','内容介绍二'declare @sql varchar(1000)
select @sql='b.tage like ''%'+replace(tage,',','%'' or b.tage like ''%')+'%''' from ta where id=1
set @sql='select * from tb b where '+@sql
exec (@sql)1 黑色,黄色,粉红 内容介绍一lz好像没有说要用content关联!