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

select
    '店铺汇总' as product_id,
    concat (
        round(
            (1 - sum(in_price * cnt) / sum(price * cnt)) * 100,
            1
        ),
        '%'
    ) as profit_rate
from
    tb_order_overall
    join tb_order_detail using (order_id)
    join tb_product_info using (product_id)
where
    date_format (event_time, '%Y%m') >= '202110'
    and shop_id = 901
union all #将两张表横向拼接
(
    select
        product_id,
        concat (
            round((1 - avg(in_price) / (avg(price))) * 100, 1),
            '%'
        ) profit_rate
    from
        tb_order_overall
        join tb_order_detail using (order_id)
        join tb_product_info using (product_id)
    where
        date_format (event_time, '%Y%m') >= '202110'
        and shop_id = 901
    group by
        product_id
    having
        (1 - avg(in_price) / (avg(price))) * 100 > 24.9
    order by
        product_id
)