+-----------+-----------+
| 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/

10-16 15:18
查看更多