MySQL查询语言之连接表

刚好碰见了一个细节满满的外连接题目，拿过来分享一下

题目描述：

编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。

返回结果表 无顺序要求 。

结果格式如下例所示。

题目分析：

很明显，输出的average_price是无法通过单一的表计算得到，所以一定会用到连接表。

我最开始写的代码为：

select 
Prices.product_id,
ifnull(round(sum(Prices.price*UnitsSold.units)/sum(UnitsSold.units),2),0) as average_price
from
Prices join UnitsSold on UnitsSold.product_id=Prices.product_id --内连接 
group by UnitsSold.product_id;

我本来以为Prices table中的product_id与UnitSold中的product_id是一一对应的关系，于是用了内连接，运行结果如下：

问题很大，答案全不对，于是我想到要看一看我连接的表到底长什么样子，于是我修改了代码，查看表的全部信息：

select 
*
from
Prices join UnitsSold on UnitsSold.product_id=Prices.product_id;

连接表中相同product_id居然有四条结果，出现了笛卡尔积现象，我觉得该连接表出现该现象不可避免，仔细分析题目， 我增加了对该表的筛选条件：between Prices.start_date and Prices.end_date，筛选出出售日期对应价格的时间段

select 
*
from
Prices join UnitsSold on UnitsSold.product_id=Prices.product_id
where (UnitsSold.purchase_date between Prices.start_date and Prices.end_date)；

现在这个表看起来没问题了。那么就根据这个表筛选

# Write your MySQL query statement below
select 
Prices.product_id,
round(sum(Prices.price*UnitsSold.units)/sum(UnitsSold.units),2) as average_price
from
Prices join UnitsSold on UnitsSold.product_id=Prices.product_id
where UnitsSold.purchase_date between Prices.start_date and Prices.end_date
group by UnitsSold.product_id;

-- round(查询内容,2)，保留两位小数
-- group by ，按照UnitsSold.product_id分组

测试案例通过，提交代码。

醒目的解答错误告诉我还有没考虑到的细节，于是我仔细分析错误案例2，得到以下错误原因

Price表中的product_id中有UnitsSold表中未出现过的id

解决办法：

将内连接改成左外连接，

将筛选条件改为where (UnitsSold.purchase_date between Prices.start_date and Prices.end_date) or UnitsSold.purchase_date is null

将查询内容改为ifnull(round(sum(Prices.price*UnitsSold.units)/sum(UnitsSold.units),2),0)

完整代码如下

# Write your MySQL query statement below
select 
Prices.product_id,
ifnull(round(sum(Prices.price*UnitsSold.units)/sum(UnitsSold.units),2),0) as average_price
from
Prices left join UnitsSold on UnitsSold.product_id=Prices.product_id
where (UnitsSold.purchase_date between Prices.start_date and Prices.end_date) or UnitsSold.purchase_date is null
group by UnitsSold.product_id;

--ifnull(查询内容，0)：查询内容为null则输出0
-- 如果筛选内容不加or UnitsSold.purchase_date is null，则会剔除空行

 

通过！