SQL查询：查找A列匹配B列4条中3条的记录及仓库缺失产品

以上为说明本次问题的示例数据。

假设表名为product_info，B列的4条待匹配值为val1、val2、val3、val4，你可替换为实际业务值，参考SQL如下：

SELECT A
FROM product_info
WHERE B IN ('val1', 'val2', 'val3', 'val4')
GROUP BY A
HAVING COUNT(DISTINCT B) = 3;

提示：若同一个A不会对应重复的B值，可将COUNT(DISTINCT B)改为COUNT(*)提升查询效率

假设存储仓库产品的表名为warehouse_product，仓库标识字段为warehouse_code，参考SQL如下：

-- 写法1：NOT IN 写法，逻辑简单易读
SELECT DISTINCT A
FROM warehouse_product
WHERE warehouse_code IN ('BB', 'CC')
AND A NOT IN (
    SELECT DISTINCT A
    FROM warehouse_product
    WHERE warehouse_code = 'AA'
);

-- 写法2：LEFT JOIN 写法，大数据量场景下性能更优
SELECT DISTINCT t1.A
FROM warehouse_product t1
LEFT JOIN warehouse_product t2
  ON t1.A = t2.A AND t2.warehouse_code = 'AA'
WHERE t1.warehouse_code IN ('BB', 'CC')
AND t2.A IS NULL;

提示：请根据实际业务替换表名、字段名和仓库编码值

内容的提问来源于stack exchange，提问作者ChefJ