select a.productid from ProductAttribute a,ProductAttribute b
where a.productid=b.productid
and a.ProductAttribute='圆形'
and b.ProductAttribute='VG'
where a.productid=b.productid
and a.ProductAttribute='圆形'
and b.ProductAttribute='VG'
from ProductAttribute a
where a.ProductAttribute='圆形'
and exists (select 1 from ProductAttribute b
where a.productid=b.productid
and b.ProductAttribute='VG'
)
(
select distinct productid from ProductAttribute where ProductAttribute = '圆形'
union all
select distinct productid from ProductAttribute where ProductAttribute = 'VG'
) t
group by productid having count(*) >= 2
insert into ProductAttribute values(44 , '圆形')
insert into ProductAttribute values(44 , 'SI')
insert into ProductAttribute values(44 , 'VG')
insert into ProductAttribute values(45 , '方形')
insert into ProductAttribute values(45 , 'SI')
insert into ProductAttribute values(45 , 'VG')
insert into ProductAttribute values(46 , '圆形')
insert into ProductAttribute values(46 , 'SI')
insert into ProductAttribute values(46 , 'VG')
goselect productid from
(
select distinct productid from ProductAttribute where ProductAttribute = '圆形'
union all
select distinct productid from ProductAttribute where ProductAttribute = 'VG'
) t
group by productid having count(*) = 2drop table ProductAttribute/*
productid
-----------
44
46(所影响的行数为 2 行)
*/
where exists (Select * From ProductAttribute Where ProductAttribute='VG')
(
select distinct productid from ProductAttribute where ProductAttribute = '圆形'
union all
select distinct productid from ProductAttribute where ProductAttribute = 'VG'
) t
group by productid having count(*) >= 2
where exists (Select * From ProductAttribute Where ProductAttribute='VG')
select productid,productattributevalue from My_ProductAttribute
44 圆形
44 SI
44 VG
44 H-J
44 黄金
44 0.3
44 夹镶45 四方形
45 SI
45 VG
45 H-J
45 白金
45 0.4
45 包镶
Select productid From (Select * From ProductAttribute Where ProductAttribute='圆形') a
where productid in (Select productid From ProductAttribute Where ProductAttribute='VG')
insert productattribute select 44,'圆形'
union all select 44,'SI'
union all select 44,'VG'
union all select 45,'方形'
union all select 45,'SI'
union all select 45,'VG'
union all select 46,'圆形'
union all select 46,'SI'
union all select 46,'VG'select productid from productattribute where productid in
(select productid from productattribute
where productattribute='圆形') and productattribute='VG'
group by productid
select distinct productid from My_ProductAttribute where
productattributevalue='圆形' and exists (select * from My_ProductAttribute
where productattributevalue='VG')and exists (select * from My_ProductAttribute
where productattributevalue='si')
根据这两组数据查询来看,表结构应该设计不太合理,一个商品一组属性应该是一记录,目前这种表结构数据查询维护很不方便
如果必须用现有表结构,可以考虑写sql先转置,再在转置基础上简单查询.
如果有表结构的话我可以试一下.
where exists (Select * From ProductAttribute b Where ProductAttribute='VG' and a.productid=b.productid)
and exists (Select * From ProductAttribute c Where ProductAttribute='方形' and a.productid=c.productid)
应该这样。