mysql - 另一个 MySQL 价格和库存比较

Question

我的原始帖子 ( MySQL price & stock comparison ) 有一个后续问题

假设我有包含以下列的 TABLE1；

表1(原始数据，未进行任何修改)

|item   |partnumber  |supplier |stock  |cost 
 CD      11A          West      11      10.11
 CD      11A          East      0       10.00
 USB     BBB          North     1       125.01
 USB     BBB          West      1       101.10

表1(显示产品价格最便宜的供应商，无论是否有库存 - 正在运行)

CODE
======
SELECT a.name , a.partnumber , a.supplier , a.cost FROM table1 a JOIN (
SELECT name , partnumber , MIN(cost) cost
FROM table1 
GROUP BY name , partnumber ) 
b ON a.name = b.name AND a.cost = b.cost AND a.partnumber = b.partnumber


RESULT
======
|item   |partnumber  |supplier   |stock |cost 
 CD      11A          East        0      10.00
 USB     BBB          West        1      101.10

但是，我想要实现的是:

如果某个供应商缺货，则向其他有库存产品的供应商展示该产品，即使价格可能不是最便宜的。 (可能是第二便宜、第三便宜等) - 如果没有供应商有库存，则无论如何向供应商显示最便宜的价格。

简而言之:库存商品优先于价格(但仍然必须是该商品的最便宜价格)，但如果根本没有人拥有该商品，则显示提供最便宜商品的供应商。

谢谢。

Answer 1

这是一个优先级查询。在其他数据库中，您可以使用row_number()。 MySQL 中没有这个选项。

实现此目的的一种方法是使用变量。这是另一种方法:

select t1.*
from table1 t1
where t1.supplier = (select tt1.supplier
                     from table1 tt1
                     where tt1.partnumber = t1.partnumber
                     order by (stock > 0) desc, -- put those with stock first
                              price asc
                     limit 1
                    );

使用变量的方法是:

select t1.*
from (select t1.*,
             (@rn := if(@pn = partnumber, @rn + 1,
                        if(@pn := partnumber, 1, 1)
                       )
             ) as rn
      from table1 t1 cross join
           (select @pn := '', @rn := 0) params
      order by partnumber, (stock > 0) desc, price
     ) t1
where rn = 1;