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

with tb2 as (
select product_id, in_price, price, cnt from(
(select * from tb_order_overall 
where status=1 and date(event_time)>='2021-10-01') as tb1
left join tb_order_detail using(order_id)
left join tb_product_info using(product_id))
where shop_id = 901)

select a, concat(b,'%') from(
(select '店铺汇总' as a, round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) b
from tb2)
union all 
(select product_id as a, round((1-sum(in_price*cnt)/sum(price*cnt))*100,1) b
from tb2
group by a having b>24.9
order by a)) as tb3

#思路：
#①先选出21年10月以后成功下订且不退货的总表，形成tb1；
#②用tb1左联明细表 再左联商品表，选出店铺是901的数据，形成tb2；
#③分组计算店铺毛利率和商品毛利率，union all联立即可。