mysql - 查询 - min(Cost) 并显示该成本的供应商

Question

我一直卡在 MySQL 查询上。该表是:

CREATE TABLE items_costs (
  UPC varchar(15) NOT NULL,
  SupplierID int(11) NOT NULL,
  Current_as_of_Date datetime DEFAULT NULL,
  Cost1 float DEFAULT NULL,
  Cost2 float DEFAULT NULL,
  Cost3 float DEFAULT NULL,
  MSRP float DEFAULT NULL,
  MAP float DEFAULT NULL,
  Unique_Entry_Id datetime DEFAULT NULL,
  PRIMARY KEY (UPC,SupplierID),
  KEY SupplierID (SupplierID),
  CONSTRAINT items_costs_ibfk_1 FOREIGN KEY (UPC) REFERENCES items (UPC),
  CONSTRAINT items_costs_ibfk_2 FOREIGN KEY (SupplierID) REFERENCES suppliers (SupplierID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8`

我得到每件商品的成本 1、成本 2、成本 3 中最低的:

MIN(LEAST(IFNULL(ic.Cost3, ic.Cost1), IFNULL(ic.Cost2, ic.Cost1), ic.Cost1)) AS Cost

但是，现在我需要获取与该特定成本关联的 SupplierID

我试过:

SELECT 
    ic.UPC,
    ic.SupplierID,
    ic.Current_as_of_Date,
    ic.Cost1 AS Cost,
    ic.MSRP,
    ic.MAP,
    s.SupplierName,
    s.Map_YN AS Supplier_MAP
FROM
    items_costs AS ic
        JOIN
    suppliers AS s ON s.supplierId = ic.SupplierID
WHERE
    (ic.upc , ic.Cost1) IN (SELECT 
            ic_min.UPC,
                MIN(LEAST(IFNULL(ic_min.Cost3, ic_min.Cost1),
                        IFNULL(ic_min.Cost2, ic_min.Cost1),
                        ic_min.Cost1)) AS Cost
        FROM
            items_costs ic_min
        GROUP BY ic_min.UPC))

...按照其他线程中的建议，按 UPC 给出最低成本。但是，仍然会得到重复的 UPC，这应该是唯一的。

我在这里做错了什么？

非常感谢!

Answer 1

你可以使用 DISTINCT

    SELECT DISTINCT 
      ic.UPC,
      ic.SupplierID,
      ic.Current_as_of_Date,
      ic.Cost1 AS Cost,
      ic.MSRP,
      ic.MAP,
      s.SupplierName,
      s.Map_YN AS Supplier_MAP
  FROM items_costs AS ic
  JOIN suppliers AS s ON s.supplierId = ic.SupplierID
  WHERE  (ic.upc , ic.Cost1) IN (
      SELECT 
              ic_min.UPC,
              MIN(LEAST(IFNULL(ic_min.Cost3, ic_min.Cost1),
                      IFNULL(ic_min.Cost2, ic_min.Cost1),
                        ic_min.Cost1)) AS Cost
          FROM items_costs ic_min
          GROUP BY ic_min.UPC))