gpt4 book ai didi

mysql - 使用 And 运算符过滤数据 MySQL

转载 作者:行者123 更新时间:2023-11-29 03:31:46 25 4
gpt4 key购买 nike

+-----------+-----------+
| product_id | tag_id |
+-----------+-----------+
| 3 | 4 |
| 3 | 29 |
| 30 | 20 |
| 3 | 19 |
| 30 | 19 |
+-----------+-----------+

我想要所有具有 tag_id 的 4 和 19 或 4 和 25 或 4 和 20 的组合的 product_ids。结果应该是 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

任何人都可以帮助我我做错了什么吗?

我也尝试了其他解决方案,它给了我正确的结果作为 product_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/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com