select * from Uto_t where prdID in (select prdID from Uto_t where ditype='尺码' and dicode like '%39%') and ditype='颜色' and dicode like '%红%'
不太明白你的意思,我看你写的sql不是已经可以查到数据了吗?不过你应该把“红,”的逗号去掉
select a.prdid from (select prdid from Uto_t where ditype = '颜色' and dicode like '%红,%') a inner join (select prdid from Uto_t where ditype = '尺码' and dicode like '%39,%') b on a.prdid = b.prdid
如果表结构是这样的话,用#2不过建议表结构变下,改成下列格式 id 尺码 颜色select * from Uto_t_New where 尺码 like '%39%' and 颜色 like '%红%'
看你结果怎么显示 --> 测试数据: [Uto_t] if object_id('[Uto_t]') is not null drop table [Uto_t] go create table [Uto_t] (prdid int,ditype varchar(4),dicode varchar(16) ) insert into [Uto_t] select 11,'颜色','红,黄,兰' union all select 11,'尺码','39,40,41' union all select 12,'颜色','红,黄' union all select 12,'尺码','40,41,42'select * from [Uto_t] a, [Uto_t] b where a.prdid=b.prdid and a.ditype='颜色' and charindex(',红,',','+a.dicode+',')>0 and b.ditype='尺码' and charindex(',39,',','+b.dicode+',')>0 prdid ditype dicode prdid ditype dicode ----------- ------ ---------------- ----------- ------ ---------------- 11 颜色 红,黄,兰 11 尺码 39,40,41(1 行受影响)
select * from Uto_t
where prdID in (select prdID from Uto_t where ditype='尺码' and dicode like '%39%')
and ditype='颜色' and dicode like '%红%'
select a.prdid from
(select prdid from Uto_t where ditype = '颜色' and dicode like '%红,%') a
inner join
(select prdid from Uto_t where ditype = '尺码' and dicode like '%39,%') b
on a.prdid = b.prdid
id 尺码 颜色select * from Uto_t_New where 尺码 like '%39%' and 颜色 like '%红%'
--> 测试数据: [Uto_t]
if object_id('[Uto_t]') is not null drop table [Uto_t]
go
create table [Uto_t] (prdid int,ditype varchar(4),dicode varchar(16) )
insert into [Uto_t]
select 11,'颜色','红,黄,兰' union all
select 11,'尺码','39,40,41' union all
select 12,'颜色','红,黄' union all
select 12,'尺码','40,41,42'select *
from [Uto_t] a,
[Uto_t] b
where a.prdid=b.prdid
and a.ditype='颜色' and charindex(',红,',','+a.dicode+',')>0
and b.ditype='尺码' and charindex(',39,',','+b.dicode+',')>0 prdid ditype dicode prdid ditype dicode
----------- ------ ---------------- ----------- ------ ----------------
11 颜色 红,黄,兰 11 尺码 39,40,41(1 行受影响)