显示汇总的不同值 [英] Showing Distinct Values with Aggregates

问题描述

我有一张桌子，用来记录不同供应商的每日价格.我的目标是找到最好的(低价)供应商. 该表的结构是 表名称:lab1 列:ID，产品ID，价格日期，价格，供应商

I have a table for recording daily price from different suppliers. My goal is to find the best (low price) supplier. The table structure is Table Name: lab1 Columns: ID, Product_ID, Price_date, Price, Supplier

-----------------------------------------------------------------------------------
ID  Product_ID  Price_date  Price   Supplier
--------------------------------------------------------------------------------------
1   8           26-10-2014  1300    SP1
2   8           05-10-2014  1600    SP2
3   8           15-10-2014  1300    SP1
4   8           14-12-2014  1200    SP3
------------------------------------------------------------------------------------------

创建表结构

CREATE TABLE clickpic_pricecompare.lab1 (
  ID int(11) NOT NULL AUTO_INCREMENT,
  Product_ID int(11) DEFAULT NULL,
  Price_Date date DEFAULT NULL,
  Price decimal(19, 2) DEFAULT NULL,
  Supplier varchar(255) DEFAULT NULL,
  PRIMARY KEY (ID)
)
ENGINE = MYISAM
COMMENT = 'testing-purpose';

INSERT INTO  lab1(ID, Product_ID, Price_Date, Price, Supplier) VALUES
(1, 8, '2014-10-26', 1300.00, 'SP1');
INSERT INTO  lab1(ID, Product_ID, Price_Date, Price, Supplier) VALUES
(2, 8, '2014-10-05', 1600.00, 'SP2');
INSERT INTO  lab1(ID, Product_ID, Price_Date, Price, Supplier) VALUES
(3, 8, '2014-10-15', 1300.00, 'SP1');
INSERT INTO  lab1(ID, Product_ID, Price_Date, Price, Supplier) VALUES
(4, 8, '2014-10-14', 1200.00, 'SP3');

我需要下面的结果外观

--------------------------------------------------------------------------------------
ID  Product_ID  Month   Price   Supplier
--------------------------------------------------------------------------------------
4   8           October 1200    SP3
-------------------------------------------------------------------------------------------

请帮助...

推荐答案

您可以将自我联接与产品ID和最低价格结合使用，以获取每个产品ID的最低价格行

You can use self join with the product id and minimum amount of price to get the lowest price row per product id

select l.ID,
l.Product_ID,
monthname(l.Price_Date) `Month`,
l.Price,
l.Supplier
from lab1 l
join (select Product_ID,min(Price) Price
     from lab1
     group by Product_ID) l1
using(Product_ID,Price)

演示

这篇关于显示汇总的不同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！