SQL87 每个供应商成本最低的产品

描述

有Products表，含有字段prod_price代表产品价格，vend_id代表供应商id

vend_id	prod_price
a0011	100
a0019	0.1
b0019	1000
b0019	6980
b0019	20

【问题】编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。

【示例结果】返回供应商id vend_id和对应供应商成本最低的产品cheapest_item。

vend_id	cheapest_item
a0019	0.1
b0019	20
a0011	100

【示例解析】

例如b0019成本最低的价格是20，且最后根据成本价格排序返回依次是a0019、b0019、a0011。

示例1

输入：

DROP TABLE IF EXISTS `Products`;

CREATE TABLE IF NOT EXISTS `Products` (

`vend_id` VARCHAR(255) NOT NULL COMMENT '供应商ID',

`prod_price` DOUBLE NOT NULL COMMENT '产品价格'

);

INSERT INTO `Products` VALUES ('a0011',100),

('a0019',0.1),

('b0019',1000),

('b0019',6980),

('b0019',20);

复制

输出：

vend_id|cheapest_item

a0019|0.100

b0019|20.000

a0011|100.000

答案

--方法一，先取出最小的价格，然后进行排序
select * from (
select vend_id,min(prod_price) as cheapest_item
from Products
group by vend_id) t1
order by cheapest_item asc

--方法二，先根据供应商id  vend_id进行分组排序,然后取出最小值
select t2.vend_id,t2.prod_price as cheapest_item
from (select t1.*,row_number() over(partition by t1.vend_id order by t1.prod_price asc) as r
from Products t1) t2
where t2.r=1
order by t2.prod_price

min()函数，取最小值

row_number() over(partition by 根据什么分区 order by 根据什么排序 排序规则asc/des )，例如：

row_number() over(partition by vend_id order by prod_price asc)含义是根据vend_id进行分区，然后相同vend_id的分到一个区，根据区里的prod_price进行升序排序。

原来表格数据是：

vend_id	prod_price
a0011	100
a0019	0.1
b0019	1000
b0019	6980
b0019	20

执行后是：

select t1.*,row_number() over(partition by t1.vend_id order by t1.prod_price asc) as r
from Products t1

vend_id	prod_price	r
a0011	100	1
a0019	0.1	1
b0019	1000	3
b0019	6980	4
b0019	20	2