SQL 中的互斥值 [英] Mutually exclusive values in SQL

问题描述

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

I have a query which selects products from a table. A product can have multiple prices (think of various prices) and a default price.

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

Naturally, this is a one-to-many relation. I need to select the products which have a given price, or the default price - which means mutual exclusion. I know this can be done through separate queries and a WHERE (not) IN clauses or a union statement, but I'm convinced a more optimal way must be possible. My query currently looks like this:

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 的一个稍微修改过的版本:

I ended up using this query, which is a slightly modified version of Gordon Linoff's:

 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

推荐答案

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

If I understand your question correctly, the products table would have the default price and the product_prices table would have any other price.

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

You want to know where the default price is being used, meaning that there are no other prices. For this, use a left outer join:

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 的记录中.在这种情况下，我会对价格表进行两次连接:

Based on your comment, you are storing the default prices in records with the business id being NULL. In this case, I would do two joins to the prices table:

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

第一个连接获取与给定价格匹配的价格.第二个获取默认价格.使用第一个结果(如果存在)，否则使用第二个结果.

The first join gets the price matching the given price. The second gets the default price. The first result is used, if present, otherwise the second is used.

这篇关于SQL 中的互斥值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！