双表关联多条件（SKU/SKU2等）匹配查询多SKU商品最低价格

改动要点 #

• 替换原有单字段匹配逻辑，新增sku2、upc、ean三个字段的匹配规则，两个表四个字段任意相等即可完成关联
• 分组逻辑从原有的按价格表sku分组，调整为按商品表的唯一标识分组，确保每个商品维度计算最低价格
• 保留原有有效商品过滤规则quantity != 0 AND active = 1

调整后SQL代码 #

SELECT p.*, x.supplier AS supplier, x.price, x.quantity AS quantity
FROM table_products p
-- 多字段任意匹配关联价格表
JOIN table_prices x 
  ON (x.sku = p.sku OR x.sku2 = p.sku2 OR x.upc = p.upc OR x.ean = p.ean)
  AND x.quantity != 0 AND x.active = 1
-- 关联子查询获取每个商品对应的最低价格
JOIN (
    SELECT p.product_id, MIN(x.price) AS min_price
    FROM table_products p
    JOIN table_prices x 
      ON (x.sku = p.sku OR x.sku2 = p.sku2 OR x.upc = p.upc OR x.ean = p.ean)
      AND x.quantity != 0 AND x.active = 1
    GROUP BY p.product_id
) y ON p.product_id = y.product_id AND x.price = y.min_price
ORDER BY category, price

提示：如果table_products没有product_id主键字段，可以将子查询中的分组字段替换为商品表实际的唯一标识字段即可；如果查询出现重复结果，可在SELECT后添加DISTINCT去重。

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