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

# 先将2021年10月以来，901店铺的信息找到
with t1 as (
    select product_id, in_price
    from tb_product_info
    where shop_id = 901
),
t2 as (
    # 找到2021-10及之后已经付款的订单的商品销售以及采购详细信息
    select a.product_id, sum(a.price * a.cnt) as total_cost, sum(t1.in_price * a.cnt) as total_account
    from t1
    right join (
        select order_id, product_id, price, cnt
        from tb_order_detail
        where order_id in (
            select order_id
            from tb_order_overall
            where date_format(event_time, '%Y-%m') >= '2021-10' and status = 1
        ) and product_id in (select product_id from t1)
    ) as a
    on a.product_id = t1.product_id
    group by a.product_id
)
# 计算每一个商品的毛利率以及店铺总体毛利率
select '店铺汇总' as product_id, concat(format((1-sum(total_account)/sum(total_cost))*100, 1), '%') as profit_rate
from t2
union all
select *
from (
    select product_id, concat(format((1-total_account/total_cost)*100, 1), '%') as profit_rate
    from t2
    where format((1-total_account/total_cost)*100, 1) > 24.9
    order by product_id
) as e;