找到正确的“记录”并仅使用该记录中的数据

Question

我有一份产品和供应商的清单。我需要确保数量大于零。如果是这样，我需要找到价格最低的产品，并列出供应商，产品(SKU)，数量和价格。

我的测试数据模式是：

create table products(merchant varchar(100), name varchar(150), quantity int, totalprice int);

insert into products values
  ('Supplier A', 'APC-SMT1000I', 10, 150),
  ('Supplier B', 'APC-SMT1000I', 15, 250), 
  ('Supplier C', 'APC-SMT1000I', 15, 350), 
  ('Supplier D', 'DEF-SMT1000I', 10, 500), 
  ('Supplier E', 'DEF-SMT1000I', 35, 350), 
  ('Supplier G', 'GHI-SMT1000I', 75, 70)

从逻辑上讲，我希望结果为：

SUPPLIER    SKU          QTY        PRICE
Supplier A  APC-SMT1000I     10       150
Supplier D  DEF-SMT1000I     35           350
Supplier G  GHI-SMT1000I     75        70

我的SQL语句是这样的：

SELECT merchant AS Supplier, name AS sku,quantity AS Qty, 
min(totalprice) AS Price FROM products where quantity > 0 group by name;

我的结果是：

SUPPLIER    SKU          QTY        PRICE
Supplier A  APC-SMT1000I     10       150
Supplier D  DEF-SMT1000I     10           350
Supplier G  GHI-SMT1000I     75        70

显然，编码是找到最低价格并显示它，但不是使用正确的数据。

我的问题？如何对数据进行分组，找到价格最低的record，并确保程序只使用该记录中的数据？

Answer 1

您可以使用以下查询：

SELECT products.*
FROM
  products INNER JOIN
  (SELECT name, MIN(totalprice) min_price
   FROM products
   WHERE quantity>0
   GROUP BY name) m
  ON products.name=m.name AND products.totalprice=min_price

在子查询中，我计算每个名称的最低总价，然后将该子查询与products表连接起来，以便只返回该名称的最低总价的行。如果有多行的最低价格，则会全部显示。

请参阅fiddle here。

Answer 2

最简单的方法是使用窗口/解析函数。您没有指定您正在使用的数据库，但这是大多数(但不是所有)数据库中可用的ANSI标准功能。

语法如下：

select merchant AS Supplier, name AS sku, quantity AS Qty, 
       totalprice AS Price
from (select p.*,
             row_number() over (partition by name
                                order by totalprice
                               ) as seqnum
      from products p
      where quantity > 0
     ) p
where seqnum = 1;