mysql - SQL 中的互斥值

Question

我有一个从表中选择产品的查询。一个产品可以有多个价格(想想各种价格)和一个默认价格。

自然，这是一对多的关系。我需要选择具有给定价格或默认价格的产品——这意味着互斥。我知道这可以通过单独的查询和 WHERE (not) IN 子句或 union 语句来完成，但我相信必须有更优化的方法。我的查询目前看起来像这样:

SELECT products.*, products_prices.price 
FROM products RIGHT JOIN 
     products_prices ON (products.id = products_prices.productId) 
WHERE products_prices.businessId = ? 
OR    products_prices.businessId IS NULL // this needs to become mutual.

编辑:我最终使用了这个查询，它是 Gordon Linoff 的稍微修改过的版本:

 SELECT distinct p.*, coalesce(pp.price, defpp.price)
 FROM products p LEFT JOIN 
      products_prices pp
      ON p.id = pp.productId and pp.businessId = ? left join
      products_prices defpp
      on p.id = defpp.productId and defpp.businessId is NULL

Answer 1

如果我对您的问题的理解正确，products 表将具有默认价格，而 product_prices 表将具有任何其他价格。

您想知道在哪里使用了默认价格，这意味着没有其他价格。为此，使用左外连接:

SELECT p.*, coalesce(pp.price, p.default_price)
FROM products p LEFT OUTER JOIN 
     products_prices pp
     ON p.id = pp.productId
WHERE pp.price = GIVENPRICE or pp.price is null

根据您的评论，您将默认价格存储在业务 ID 为 NULL 的记录中。在这种情况下，我会对价格表进行两次联接:

SELECT p.*, coalesce(pp.price, defpp.price)
FROM products p LEFT OUTER JOIN 
     products_prices pp
     ON p.id = pp.productId and pp.price = GIVENPRICE left outer join
     products_prices defpp
     on p.id = defpp.productId and defpp.businessId is NULL

第一个加入获得匹配给定价格的价格。第二个获取默认价格。如果存在，则使用第一个结果，否则使用第二个。