显示汇总的不同值 [英] 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屋!
查看全文