MySQL基础练习题9-平均售价

题目·：查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。

准备数据

​分析数据

第一步：用左连接连接两张表

​第二步：留下符合日期

第三步：算平均售价

第四步：排除那些售卖为0的这种情况

总结：

题目：查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。

准备数据

## 创建库
create database db;
use db;

## 创建价格表（prices）
Create table If Not Exists Prices (product_id int, start_date date, end_date date, price int);

## 创建单价表（unitssold）
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');
insert into Prices (product_id, start_date, end_date, price) values ('3', '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');

 输入：

价格表

单价表

 分析数据

平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96

 第一步：用左连接连接两张表

## 第一步：先将两张表进行左连接
select * from prices p left join unitssold u on p.product_id = u.product_id;

 第二步：留下符合日期

## 第二步：留下符合日期
select * from prices p left join unitssold u on p.product_id = u.product_id
where u.purchase_date between p.start_date and p.end_date ;

 第三步：算平均售价

## 第三步：算平均售价
select p.product_id,round(sum(units * price) / sum(units),2) as average_price
from prices p left join unitssold u on p.product_id = u.product_id
where u.purchase_date between p.start_date and p.end_date
group by p.product_id;

第四步：排除那些售卖为0的这种情况

## 排除那些售卖为0的这种情况
select p.product_id,round(ifnull(sum(units * price) / sum(units),0),2) as average_price
from prices p left join unitssold u on p.product_id = u.product_id
where u.purchase_date between p.start_date and p.end_date or u.product_id is null
group by p.product_id;

总结：

1. 遇到这种需要两列进行计算，还要排除符合A表而不符合B表，可以进行A表左连接B表。把不符合条件的变成null，最后用ifnull()函数排除null值的列。
2. 最后结果需要计算，且有小数点。使用ROUND(column_name,decimals)，column_name: 要舍入的字段，必需、decimals: 规定要返回的小数位数，可选。