mysql - 选择 ID

我有表:

product_id | filter_id
        68 | 2
        68 | 4
        66 | 1
        69 | 4
        67 | 1
        67 | 5

我想得到

`product_id` WHERE `filter_id` = '2' AND `filter_id` = '4'

即，我只需要得到 product_id = 68

SELECT `product_id` FROM `filter` WHERE `filter_id` IN (2,4) - unsuitable

我怎样才能做到这一点？

最佳答案

您可以使用 group by 选择所有具有 filter_id 2 和 4 的 product_ids

select product_id
from mytable
where filter_id in (2,4)
group by product_id
having count(*) = 2

如果 (product_id, filter_id) 不唯一，则使用 count(distinct filter_id) = 2 确保产品同时具有 filter_id