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

WITH t1 AS(SELECT
'店铺汇总' AS product_id,
CONCAT(ROUND((1-SUM((b.in_price+0)*(a.cnt+0))/SUM(a.price*a.cnt))*100,1),'%') AS profit_rate
FROM tb_order_detail a
JOIN tb_product_info b USING(product_id)
JOIN tb_order_overall c USING(order_id)
WHERE b.shop_id = 901 AND DATE(c.event_time) BETWEEN '2021-10-01' AND DATE(NOW())
GROUP BY b.shop_id),

t2 AS (SELECT
b.product_id AS product_id,
CONCAT(ROUND((1-SUM((b.in_price+0)*(a.cnt+0))/SUM(a.price*a.cnt))*100,1),'%') AS profit_rate
FROM tb_order_detail a
JOIN tb_product_info b USING(product_id)
JOIN tb_order_overall c USING(order_id)
WHERE b.shop_id = 901 AND DATE(c.event_time) BETWEEN '2021-10-01' AND DATE(NOW())
GROUP BY b.product_id
HAVING ROUND((1-SUM((b.in_price+0)*(a.cnt+0))/SUM(a.price*a.cnt))*100,1)>24.9 
ORDER BY b.product_id)
# 订单总金额是订单的，一个订单可以包含多个店铺的商品，题目只要求901商铺。所以不用第二张订单总额表

SELECT
*
FROM t1
UNION
SELECT
*
FROM t2