sql - 如何从两个只有 ID 重叠的表中获取最大值？

Question

我有两个不同的表头(缩短)

表a

supplier_id, company_name

表b

product_id, product_name, supplier_id, unit_price

我必须制作一张只有每家公司产品单价最高的表格。结果表的标题是:company_name, product_name, unit_price。

我的代码实际上是选择每个产品。

SELECT suppliers.company_name AS company, products.product_name AS product, MAX(products.unit_price) AS price FROM suppliers
JOIN products
    ON products.supplier_id = suppliers.supplier_id
GROUP BY company_name, product_name, unit_price
ORDER BY price DESC;

Company     Product     Max unit price
company a   product x      value 1
company b   product y      value 2

Answer 1

demo:db<>fiddle

您似乎正在搜索 DISTINCT ON。这给出了有序组的第一条记录。在您的情况下，组是公司名称，其中的顺序是价格(按降序排列，首先获得最高值):

SELECT DISTINCT ON (suppliers.company_name) 
    suppliers.company_name AS company, 
    products.product_name AS product, 
    products.unit_price AS price 
FROM suppliers
JOIN products
    ON products.supplier_id = suppliers.supplier_id
ORDER BY suppliers.company_name, products.unit_price DESC;