MySQL基于多列多条件匹配查询product_id的问题求助

问题分析 #

你需要查询的是同时存在两条指定记录的product_id：一条满足product_category_variants_id = 1 AND product_category_variants_value_id = 2，另一条满足product_category_variants_id = 2 AND product_category_variants_value_id = 5。

原查询语句用AND连接两个互斥条件（单条记录的product_category_variants_id不可能同时等于1和2），导致无法返回正确结果。

正确查询方法 #

方法1：分组统计 + HAVING筛选 #

通过分组统计每个product_id匹配指定条件的记录数，筛选出同时满足两个条件的id：

SELECT product_id
FROM product_variants
WHERE 
    (product_category_variants_id = 1 AND product_category_variants_value_id = 2)
    OR 
    (product_category_variants_id = 2 AND product_category_variants_value_id = 5)
GROUP BY product_id
HAVING COUNT(DISTINCT product_category_variants_id) = 2;

方法2：自连接查询 #

将表自连接，分别匹配两个条件：

SELECT DISTINCT pv1.product_id
FROM product_variants pv1
JOIN product_variants pv2 
    ON pv1.product_id = pv2.product_id
WHERE 
    pv1.product_category_variants_id = 1 AND pv1.product_category_variants_value_id = 2
    AND 
    pv2.product_category_variants_id = 2 AND pv2.product_category_variants_value_id = 5;

方法3：子查询+EXISTS #

用EXISTS判断每个product_id是否同时存在两条符合条件的记录：

SELECT DISTINCT product_id
FROM product_variants pv
WHERE 
    EXISTS (
        SELECT 1 FROM product_variants 
        WHERE product_id = pv.product_id 
          AND product_category_variants_id = 1 
          AND product_category_variants_value_id = 2
    )
    AND
    EXISTS (
        SELECT 1 FROM product_variants 
        WHERE product_id = pv.product_id 
          AND product_category_variants_id = 2 
          AND product_category_variants_value_id = 5
    );

以上三种方法均可返回你期望的结果：6、7、8。

内容的提问来源于stack exchange，提问作者MUHSIN MOHAMED PC