1251 平均售价（简单）

最新推荐文章于 2024-07-24 19:03:45 发布

每天都要被自己菜醒

最新推荐文章于 2024-07-24 19:03:45 发布

阅读量165

点赞数

分类专栏： leetcode 数据库刷题文章标签： mysql

本文链接：https://blog.csdn.net/qq_45531594/article/details/108412927

版权

数据库刷题同时被 2 个专栏收录

38 篇文章 3 订阅

订阅专栏

leetcode

21 篇文章 0 订阅

订阅专栏

Create table If Not Exists Prices (product_id int, start_date date, end_date date, price int)
Create table If Not Exists UnitsSold (product_id int, purchase_date date, units int)
Truncate table Prices
insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-02-17', '2019-02-28', '5')
insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-03-01', '2019-03-22', '20')
insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-01', '2019-02-20', '15')
insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-21', '2019-03-31', '30')
Truncate table UnitsSold
insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-02-25', '100')
insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-03-01', '15')
insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-02-10', '200')
insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-03-22', '30')

在这里插入图片描述

select 
#*,price*units as 'num'
a.product_id,round( sum(price*units)/sum(units) ,2 ) as 'average_price'

from  Prices a 
left join  UnitsSold b 
on a.product_id= b.product_id  and # purchase_date >=start_date and purchase_date <=end_date
#购买日期在 价格起始日期和结束日期之内
purchase_date between start_date and end_date
group by a.product_id