一个产品可以属于多个分类,表结构如下:
goods_id category_id
1 3
1 4
2 3上面的产品2属于分类3,但产品1,同时属于分类3和4.
如何能查出同时属于多个分类的产品?比如查同时属于分类3和4的产品时,结果只有1.多谢
goods_id category_id
1 3
1 4
2 3上面的产品2属于分类3,但产品1,同时属于分类3和4.
如何能查出同时属于多个分类的产品?比如查同时属于分类3和4的产品时,结果只有1.多谢
from 表结构如下 a inner join 表结构如下 b on a.goods_id=b.goods_id
where a.category_id=3 and b.category_id=4
from 表结构如下 a
where category_id=3
and exists (select 1 from 表结构如下 where goods_id=a.goods_id and category_id=4)
and exists (select 1 from 表结构如下 where goods_id=a.goods_id and category_id=5)
...
and exists (select 1 from 表结构如下 where goods_id=a.goods_id and category_id=X)
这个1是什么意思?
from (
select goods_id,category_id
from mytable
group by goods_id,category_id
) r
group by goods_id
having count(*)>1;