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

店铺汇总与某个商品汇总的的查询逻辑不同，但是查询结果的表结构相同，所以此时可以用union

with
    t1 as (
    select
    od.product_id,
    sum(price*cnt)as price,表示商品在这条订单中的总定价
    pi.in_price*sum(cnt) as in_price,表示商品在这条订单中的总竞价
    shop_id
from
    tb_product_info pi right join tb_order_detail od
    on pi.product_id=od.product_id left join tb_order_overall oo
    on oo.order_id=od.order_id
where date(event_time) >= '2021-10-01' and shop_id=901 and status=1
group by od.product_id,pi.in_price
)三表连接，过滤条件

这里指的是店铺汇总那一行
select 
    '店铺汇总' as product_id,
    concat(ROUND(ROUND(1-sum(in_price)/sum(price), 3)*100,1),'%') AS profit_rate
from t1
group by shop_id

union
union进行连接
(select
    cast(product_id as char) product_id,
    concat(ROUND(ROUND(1-sum(in_price)/sum(price), 3)*100,1),'%') AS profit_rate
from t1
group by product_id

having ROUND(1-sum(in_price)/sum(price), 3) > 0.249
order by product_id);



总得来说感觉并不难。