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

本题的难度在于将店铺汇总信息和查询结果汇总起来,需要用到union连接

需要一定的刷题知识,见过此类型题之后再做就不难了

WITH t1 as( -- 第一条查出符合时间的条
    select d.*
    FROM tb_order_detail d
        JOIN tb_order_overall too
            ON d.order_id = too.order_id
    WHERE DATE_FORMAT(event_time,'%Y-%m')>='2021-10'
),t2 as( -- 第二个表找出符合店铺的条数 
    SELECT
           t1.*,
           in_price,
           in_price*t1.cnt s_inprice,
           price*cnt s_price
    FROM t1 JOIN tb_product_info tp
    ON t1.product_id=tp.product_id
    WHERE shop_id=901
),t3 as( -- 第三个找出符合该题的条件商品的利率
    SELECT
           product_id,
           concat(round((1-sum(s_inprice)/sum(s_price))*100,1),'%') profit_rate
    FROM t2
    GROUP BY product_id
    HAVING profit_rate> 29.3
    order by profit_rate
)
-- 最后添加一行信息通过union将店铺汇总和表3的结果合并即可
SELECT '店铺汇总' AS product_id,
       concat(round((1-sum(s_inprice)/sum(s_price))*100,1),'%') profit_rate
FROM t2
UNION
select * FROM t3