题解 | #某店铺的各商品毛利率及店铺整体毛利率#

with t1 as(
select tbl.order_id,tbl.product_id,price,cnt,tb.event_time,in_price
from tb_order_detail tbl
left join tb_product_info tbo 
on tbo.product_id =tbl.product_id 
left join tb_order_overall tb
on tbl.order_id=tb.order_id
where date_format(event_time,"%Y-%m") >= '2021-10' and shop_id =901
)

select product_id,concat(profit_rate,'%') as profit_rate
from (

(select '店铺汇总' as product_id, round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) as profit_rate
from t1)
union 
(select product_id,round((1 -sum(in_price*cnt)/sum(price*cnt))*100,1) as profit_rate
from t1
group by product_id
having profit_rate > 24.9
order by profit_rate
) 
) t2

一、分析题目要求：

1. 2021年10月以来 这是一个筛选时间的 where过滤条件
2. 店铺901 同样是where过滤条件
3. 商品毛利率 需要计算 涉及到进货价格 收获价格 销售数量
4. 总体毛利率 需要计算 涉及到上述三个

二、思路

1. 先以第三张表为枢纽，左连接两张表 筛选出满足时间和商店要求的信息

with t1 as(
select tbl.order_id,tbl.product_id,price,cnt,tb.event_time,in_price
from tb_order_detail tbl
left join tb_product_info tbo 
on tbo.product_id =tbl.product_id 
left join tb_order_overall tb
on tbl.order_id=tb.order_id
where date_format(event_time,"%Y-%m") >= '2021-10' and shop_id =901
)

2.联合查询

1. 先查询总体的毛利率，用sum函数计算单价和数量乘积

2. 在根据产品id分类计算各个产品的毛利率

3. 通过联合查询 将两个数据放到一起 这里有个细节 因为单独的商品毛利率是升序 总体毛利率要放在最上面 我们可以用括号分隔开

(select '店铺汇总' as product_id, round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) as profit_rate
from t1)
union 
(select product_id,round((1 -sum(in_price*cnt)/sum(price*cnt))*100,1) as profit_rate
from t1
group by product_id
having profit_rate > 24.9
order by profit_rate
) 

3.最后使用concat函数 增加百分号即可