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

注意问题的分解：

• 店铺汇总和商品汇总不在同一个维度下，考虑用union all将两者聚合；
• 时间区间和订单状态只能在tb_order_overall中通过order_id关联，考虑用in+子查询的方式；
• 结果集中带有%，应为字符串的输出类型，可以用concat把‘%’加上；
• 只显示一定范围内的产品，考虑用having子句

select '店铺汇总',
concat(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100,1), '%')
from (
select product_id, price, cnt
from tb_order_detail 
where order_id in 
(select order_id from tb_order_overall
where status = 1 and event_time > '2021-10-01') 
    ) t1
left join tb_product_info t2
on t1.product_id = t2.product_id 
where t2.shop_id = 901
union all 
(
select t1.product_id,
concat(round((1 - avg(t2.in_price) / avg(t1.price )) * 100, 1), '%') 
from tb_order_detail t1 
left join tb_product_info t2 
on t1.product_id = t2.product_id 
where t2.shop_id = 901 
group by 1
having (1 - avg(t2.in_price) / avg(t1.price )) * 100 > 24.9  
order by 1 asc
)