【面试题】
某店铺的商品信息表中记录了有哪些商品
订单明细表中记录了商品销售的流水；"订单明细表"中的'商品ID' 与"商品信息表"中的'商品ID'一一对应。
问题：计算商品A，商品B，在2019年的总销售额和每月销售额
【解题思路】
我们来分析一下问题，销售额 = 商品单价 * 数量。
其中，商品单价在商品信息表中，商品数量在订单明细表中。这涉及到两个表，要想到《猴子从零学会SQL》里讲过的要用到多表联结。
用多维度拆解分析方法来拆解下问题，销售额需要满足：是商品A和商品B的销售，销售时间是19年，订单状态为已支付。
要分别计算商品A和商品B的销售，需要使用分组统计。因此这个问题可以分成3步，下面来详细说明下每个步骤。
1.多表联结
要用哪种联结呢？
因为要保留订单明细表中所有的数据，因此我们选择以订单明细表为左表，进行左联结。联结键为商品ID。
select *from 订单明细表 as a1left join 商品信息表 as a2on a1.商品ID = a2.商品ID;
查询结果（t1)：
2.条件筛选
问题中的限定条件有2个：
1）商品名称为商品A和商品B
2）订单状态为支付
订单明细表中都是2019年数据，所以不需再额外限定。
select *from 订单明细表 as a1left join 商品信息表 as a2on a1.商品ID = a2.商品IDwhere 订单状态 = '支付'and 商品名称 in ('商品A','商品B');
查询结果（t2)：
3.分组汇总
1）如何分组
分组要使用到group by子句。本题按照商品名称分组，也就是group by 商品名称。
select *from 订单明细表 as a1left join 商品信息表 as a2on a1.商品ID = a2.商品IDwhere 订单状态 = '支付'and 商品名称 in ('商品A','商品A')group by 商品名称;
2）计算19年总销售和19年各月总销售
计算19年总销售。单个订单的销售额 = 订单数 * 单价，使用sum(订单数*单价)即可得到19年总销售额。
select 商品名称,sum(订单数*单价) as YTDfrom 订单明细表 as a1left join 商品信息表 as a2on a1.商品ID = a2.商品IDwhere 订单状态 = '支付'and 商品名称 in ('商品A','商品A')group by 商品名称;
查询结果：
再来计算19年各月总销售。我们先以19年1月销售为例。
select 商品名称,sum(订单数*单价) as YTD,sum(if(substr(支付时间,1,7)= '2019-01',销量*单价,0)) as "1月"from 订单明细表 as a1left join 商品信息表 as a2on a1.商品ID = a2.商品IDwhere 订单状态 = '支付'and 商品名称 in ('商品A','商品A')group by 商品名称;
查询结果：
我们来理解一下上面的sum(if(substr(支付时间,1,7)= '2019-01',销量*单价,0)) as "1月" 这段语句。
筛选结果表t2包含19年所有数据，如果直接求和显然不符题意。要计算1月份的销售额，要在聚合前加上if条件判断函数。
if函数有3个参数,第1个参数进行条件判断，若条件判断结果为真，则返回结果expr1，否则返回结果expr2。
我们使用if函数对支付时间进行判断，如果支付时间为2019-01，那么等于销量*单价，否则为0。
此时我们发现，21年1月以外的销售值，都变成了0。再对销售求和即可得到19年1月的销售额，即sum(if(substr(支付时间,1,7)= '2021-01',销量*单价,0)) as "1月"。
用同样的方法可以计算出2~12月的销售额。
本题最终的sql如下：
select 商品名称,sum(订单数*单价) as YTDsum(if(substr(支付时间,1,7)= '2019-01',销量*单价,0)) as "1月",sum(if(substr(支付时间,1,7)= '2019-02',销量*单价,0)) as "2月",sum(if(substr(支付时间,1,7)= '2019-03',销量*单价,0)) as "3月",sum(if(substr(支付时间,1,7)= '2019-04',销量*单价,0)) as "4月",sum(if(substr(支付时间,1,7)= '2019-05',销量*单价,0)) as "5月",sum(if(substr(支付时间,1,7)= '2019-06',销量*单价,0)) as "6月",sum(if(substr(支付时间,1,7)= '2019-07',销量*单价,0)) as "7月",sum(if(substr(支付时间,1,7)= '2019-08',销量*单价,0)) as "8月",sum(if(substr(支付时间,1,7)= '2019-09',销量*单价,0)) as "9月",sum(if(substr(支付时间,1,7)= '2019-10',销量*单价,0)) as "10月",sum(if(substr(支付时间,1,7)= '2019-11',销量*单价,0)) as "11月",sum(if(substr(支付时间,1,7)= '2019-12',销量*单价,0)) as "12月"from 订单明细表 as a1left join 商品信息表 as a2on a1.商品ID = a2.商品IDwhere 订单状态 = '支付'and 商品名称 in ('商品A','商品A')group by 商品名称;
查询结果:
【本题考点】
1.遇到问题要学会要用多维度拆解分析方法来拆解
2.涉及到多张表要想到使用多表联结。
3.考察分组求和知识点。
4.考察了if函数的灵活使用。
【举一反三】
计算商品A在2019年的总销售额和各地区销售额。
参考答案：
select 商品名称,sum(订单数*单价) as "总销售",sum(if(区域 = '华南',销量*单价,0)) as "华南",sum(if(区域 = '华东',销量*单价,0)) as "华东",from 订单明细表asa1left join 商品信息表 asa2on a1.商品ID = a2.商品IDwhere 订单状态 = '支付'and 商品名称 = '商品A'group by 商品名称;
查询结果：
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
