mysql - 选择最具体的加入结果

我试图让mysql返回一个shopitem的最具体的折扣，因为可能会有几个折扣。我的表和数据如下（不相关的列已被省略）：

Item
  itemId   brand    supplier   price
  ======   =====    ========   =====
  Item1    Brand1   Supply1    100
  Item2    Brand2   Supply1    100
  Item3    Brand3   Supply1    100
  Item4    Brand4   Supply2    100

Discount
  discountId   itemId   brand    supplier   discountPrice
  ==========   ======   ======   ========   =============
  Discount1    (null)   (null)   Supply1    80
  Discount2    (null)   Brand2   Supply1    60
  Discount3    Item3    (null)   (null)     40

我希望查询的输出是

itemId  price  discountPrice
===================================
Item1   100    80
Item2   100    60
Item3   100    40
Item4   100    (null)

如你所见我的规则是
供应商折扣最低
供应商+品牌折扣更具体
ItemId折扣是最具体的
但是，带有或在子句上的正常左联接将返回所有组合，而不是最具体的折扣。我怎样才能做到这一点？
select item.itemId, item.price, discount.discountPrice from item left join discount on (item.itemId = discount.itemId) or (item.brand = discount.brand and item.supplier = discount.supplier) or (item.supplier = discount.supplier AND discount.brand IS NULL)

最佳答案

我想：
（AA）

SELECT i.itemId,
       i.price,
       COALESCE(d.discountPrice, d2.discountPrice, d3.discountPrice) AS discountPrice
FROM item i
LEFT JOIN discount d
  ON i.itemId = d.itemId
LEFT JOIN discount d2
  ON i.brand = d2.brand
  AND i.supplier = d2.supplier
LEFT JOIN discount d3
 ON i.supplier = d3.supplier
 AND d3.brand IS NULL

结果：

| ITEMID | PRICE | DISCOUNTPRICE |
----------------------------------
|  Item1 |   100 |            80 |
|  Item2 |   100 |            60 |
|  Item3 |   100 |            40 |
|  Item4 |   100 |        (null) |

关于mysql - 选择最具体的加入结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17588538/