1.问题描述
商家卖货记录表t5包含一下字段
- 卖家id：seller_id
- 买家id：buyer_id
- 物品id：item_id
- 物品数量：num
要求计算每个卖家销量最高的商品，输出信息包含一项内容：卖家id、物品id、物品总销量。
2.初始化脚本
-- 初始化t5
create table t5(seller_id int,buyer_id int,item_id int ,num int );
insert into t5 values(1,11,1,100),(1,12,1,200),(1,12,2,300);
insert into t5 values(2,11,3,100),(2,12,4,200);
3.思路分析
1.首先我们可以查询出每个商家每个物品的总销量。
select seller_id,buyer_id, sum(num) as total from t5 group by seller_id,item_id;
得到如下结果：
2.基于上述结果来计算商家卖的物品的排名，这里我使用rank()来计算排名。
select seller_id,buyer_id, total ,rank() over(partition by seller_id order by total desc) as rk
from (select seller_id,buyer_id, sum(num)as total from t5 group by seller_id,item_id ) s;
得到如下结果：
3.所以最畅销的产品的条件就是rk=1。
select seller_id,buyer_id,total
from(select seller_id,buyer_id, total ,rank() over(partition by seller_id order by total desc) as rk
from (select seller_id,buyer_id, sum(num)as total from t5 group by seller_id,item_id ) s) t
where t.rk=1;
得到如下结果:
