+-----------+-----------+
| product_id | tag_id |
+-----------+-----------+
| 3 | 4 |
| 3 | 29 |
| 30 | 20 |
| 3 | 19 |
| 30 | 19 |
+-----------+-----------+
我要所有的产品id,有标签id的组合4和19或4和25或4和20。结果应该是3,而不是3和30,使用以下查询:
select product_id
from product_tag_mapping
where tag_id in ('4','19') or tag_id in (4,29) or tag_id in (4,20)
group by product_id
having count(distinct(tag_id)) >= 2
有人能帮我什么我做错了吗?
我也尝试了其他的解决方案,得到了正确的结果,如产品id'3',但是查询非常大,这对于大数据库来说是很困难的:
SELECT DISTINCT product_id
FROM product_tag_mapping AS t
WHERE EXISTS
( SELECT *
FROM product_tag_mapping AS t1
WHERE t1.product_id = t.product_id
AND t1.tag_id = 4
)
AND EXISTS
( SELECT *
FROM product_tag_mapping AS t2
WHERE t2.product_id = t.product_id
AND t2.tag_id = 19
)
OR EXISTS
( SELECT *
FROM product_tag_mapping AS t2
WHERE t2.product_id = t.product_id
AND t2.tag_id = 4
)
AND EXISTS
( SELECT *
FROM product_tag_mapping AS t2
WHERE t2.product_id = t.product_id
AND t2.tag_id = 20
)
OR EXISTS
( SELECT *
FROM product_tag_mapping AS t2
WHERE t2.product_id = t.product_id
AND t2.tag_id = 4
)
AND EXISTS
( SELECT *
FROM product_tag_mapping AS t2
WHERE t2.product_id = t.product_id
AND t2.tag_id = 29
)
如有任何帮助,我们将不胜感激。
最佳答案
一种捷径是使用having子句中的条件和作为
select
product_id from product_tag_mapping
group by product_id
having
( sum(tag_id=4)= 1 and sum(tag_id=19)=1 )
or ( sum(tag_id=4)= 1 and sum(tag_id=29)=1 )
or ( sum(tag_id=4)= 1 and sum(tag_id=20)=1 ) ;
如果一个产品多次出现相同的
tag_id
,您可以使用>= 1
而不是=1
关于mysql - 使用And运算符MySQL过滤数据,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29764955/