【场景】：店铺分析、行合并

【分类】：行合并

分析思路

难点：

1.结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率

2.吊牌总金额：产品数量*吊牌单价，即 sum(sales_num * tag_price)

(
select 查询结果 ['店铺汇总';店铺毛利率]
from 从哪张表中查询数据[多表连接]
)
union
(
select 查询结果 [商品ID;商品毛利率]
from 从哪张表中查询数据[多表连接]
where 查询条件 [商品毛利率大于24.9%]
order by 对查询结果排序 [先输出店铺毛利率，再按商品ID升序];
)

求解代码

方法一

with子句

with
    main as(
        #计算店铺毛利率
        select
            '店铺汇总' as product_id,
            concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),"%") as profit_rate
        from tb_product_info
        join tb_order_detail using(product_id)
        join tb_order_overall using(order_id)
        where status <> 2
        and shop_id = 901
        and date(event_time) >= '20211001'
        group by shop_id
    )
    ,attr as(
        #计算商品毛利率
        select
            product_id,
            concat(round((1-sum(in_price)/sum(price))*100,1),"%") as profit_rate
        from tb_product_info
        join tb_order_detail using(product_id)
        join tb_order_overall using(order_id)
        where status <> 2
        and shop_id = 901
        and date(event_time) >= '20211001'
        group by product_id
    )
#难点：结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率
(
    select
        product_id,
        profit_rate
    from main
)
union
(
    select
        product_id,
        profit_rate
    from attr
    where replace(profit_rate,'%','') > 24.9
    order by substring(1,2) desc,product_id
)

方法二

多表连接

#计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率
(		
    #计算店铺毛利率
    select
        '店铺汇总' as product_id,
        concat(round((1-sum(in_price*cnt)/sum(price*cnt))*100,1),"%") as profit_rate
    from tb_product_info
    join tb_order_detail using(product_id)
    join tb_order_overall using(order_id)
    where status <> 2
    and shop_id = 901
    and date(event_time) >= '20211001'
    group by shop_id
)
union
(
    #计算商品毛利率;商品毛利率大于24.9%
    select
        product_id,
        concat(round((1-sum(in_price)/sum(price))*100,1),"%") as profit_rate
    from tb_product_info
    join tb_order_detail using(product_id)
    join tb_order_overall using(order_id)
    where status <> 2
    and shop_id = 901
    and date(event_time) >= '20211001'
    group by product_id having round((1-sum(in_price)/sum(price))*100,1) > 24.9
    order by substring(1,2) desc,product_id
)