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

with
    t1 as (
        select
            q3.product_id as product_id,
            in_price,
            price,
            if(status =2 ,-cnt,cnt) as cnt,
            quantity,
            price * cnt as sr
        from
            tb_product_info as q1
            join tb_order_detail as q3 on q1.product_id = q3.product_id
            join tb_order_overall as q2 on q2.order_id = q3.order_id
        where
            shop_id = 901
            and DATE_FORMAT(event_time,'%Y-%m')>='2021-10'
        order by
            product_id
    )
select
    '店铺汇总' as product_id,
    concat (
        round(
            (1 - sum(in_price * cnt) / sum(price * cnt)) * 100,
            1
        ),
        '%'
    ) as profit_rate
from
    t1
union all
select
    product_id,
    concat(round(
            (1 - sum(in_price*cnt) /sum(price*cnt)) * 100,
            1
        ),
        '%'
    )as profit_rate
from
    t1
group by
    product_id
having
    round(
            (1 - sum(in_price*cnt) /sum(price*cnt)) * 100,
            1
        ) > 24.9;

我在限制日期时一开始用的是date（event_time）>=2021-10试运行通过了，提交时失败了，小错误总是不断。