mysql - 选择 id WHERE

Question

我有表格:

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

我该怎么做？

Answer 1

您可以使用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 的