-
先找到店铺所有商品id,进价,售价,销售数量,生成子表a
-
联结:using(product_id),using(order_id)
-
条件:where..and...
-
2021年10月以来
-
店铺901
-
支付状态: status = 1
-
从a中计算店铺整体毛利率:
-
毛利率::(1-总进价成本/总销售收入)*100%
-
从a中计算商品毛利率大于24.9%的商品信息
-
毛利率::(1-总进价成本/总销售收入)*100%
-
分组:group by product_id
-
条件:having
-
排序:order by
-
合并上面的结果:union
-
易错点:
with a as(
select
product_id,
in_price,
price,
cnt
from tb_product_info
left join tb_order_detail using(product_id)
left join tb_order_overall using(order_id)
where date_format(event_time,'%Y%m') >= '202110'
and shop_id = '901'
and status = 1
)
(
select
'店铺汇总' as product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from a
)
union
(
select
product_id,
concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),'%') as profit_rate
from a
group by product_id
having profit_rate > 24.9
order by product_id
)