利用intersect求交集 select * from t_typeproduct where 分类id=1 intersect select * from t_typeproduct where 分类id=2
根据你的图片,感觉你的表设计有问题,你的 分类表 t_keytype , 应该加一个这个就很好了,逻辑很清楚. 下面的也可以: select * from t_product a ,(select pid from t_typeproduct b,t_keytype c where b.tid=c.tid and c.name in ('分类1','分类2') group by b.pid having count(*)= (select count(*) from t_keytype where name in ('分类1','分类2') )) b where a.pid=b.pid
select * from t_typeproduct where 分类id=1
intersect
select * from t_typeproduct where 分类id=2
应该加一个这个就很好了,逻辑很清楚.
下面的也可以:
select * from t_product a ,(select pid from t_typeproduct b,t_keytype c
where b.tid=c.tid and c.name in ('分类1','分类2')
group by b.pid
having count(*)= (select count(*) from t_keytype where name in ('分类1','分类2') )) b
where a.pid=b.pid