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

with s901 as (
    select
    p.product_id,
    p.shop_id,
    p.in_price,
    d.price,
    d.cnt
from
    tb_product_info as p
join
    tb_order_detail as d
    on p.product_id = d.product_id
join
    tb_order_overall as o
    on o.order_id = d.order_id
where
    p.shop_id = 901
    and date(o.event_time) >= '2021-10-01'
)
select
    '店铺汇总' as product_id,
    concat(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100, 1), '%') as profit_rate
from
    s901
group by
    shop_id

union all # 不去重,不排序

select
    CAST(product_id AS CHAR) as product_id,
    concat(round((1 - sum(in_price * cnt) / sum(price * cnt)) * 100, 1), '%') as profit_rate
from
    s901
group by
    product_id
having
    profit_rate > 24.9
order by
    CASE WHEN product_id = '店铺汇总' THEN 0 ELSE 1 END, # 给 “店铺汇总” 这行打排序标记 0，其他商品打 1 按升序（ASC）排序 
    product_id ASC;