MySQL 比较价格 [英] MySQL Compare prices

问题描述

我正在努力创建一个包含多个供应商的价格比较表.

I am struggeling with creating a price comparison table with multiple suppliers.

该表具有以下列 - 让我们称之为 table1:

The table has the following columns - lets call it table1:

|name   |partnumber |supplier  |cost 
CD      11A          West       10.11
CD      11A          East       10.00
USB     BBB          North      125.01
USB     BBB          West       101.10

我想要实现的是让 table1 显示以下内容:

What I would like to achieve is for table1 to show the following:

|name   |partnumber |supplier  |cost 
CD      11A          East       10.00
USB     BBB          West       101.10

结果/差异是:该表将仅显示与以最便宜的价格提供产品的供应商相关的列.

Result/difference being: The table would only show the colums related to the supplier that has the product at its cheapest price.

有人介意帮我一把吗?它一直困扰着我.

Is there anyone who would mind giving me a hand with it? It has been doing my head in.

或者；我还为每个供应商创建了另一个带有价格列的表，但这并没有使数据库标准化，也不利于编写良好的查询或性能.

Alternatively; I have also created another table with a price column for each supplier, but that isn't standardising the database and it wouldn't make for a well written query or performance.

谢谢！

推荐答案

这个查询你需要什么

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

您需要按(名称和零件编号)对结果进行分组，并找到每组的最低成本

you need to group the result by(name and partnumber) and find the minimum cost for each group

这篇关于MySQL 比较价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！