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

WITH three_table_join AS(
    SELECT
        t2.product_id,t2.in_price,
        t3.total_cnt,t3.status,
        t1.price,t1.cnt
    FROM
        tb_order_detail t1
    JOIN
        tb_product_info t2
    ON t1.product_id = t2.product_id
    JOIN
        tb_order_overall t3
    ON t1.order_id = t3.order_id
    WHERE DATE_FORMAT(t3.event_time, '%Y%m') >= '202110'
    AND t2.shop_id = '901'
)
SELECT
     shop_total product_id,CONCAT(ROUND((1 - SUM(in_price * cnt) / SUM(price * cnt))*100,1),'%') profit_rate
FROM(
    SELECT
       *,'店铺汇总' AS  shop_total
    FROM
        three_table_join
)tmp
GROUP BY shop_total
UNION ALL
SELECT *
FROM(
	SELECT
		product_id,CONCAT(ROUND((1 - SUM(in_price * cnt) / SUM(price * cnt))*100,1),'%') profit_rate
	FROM
		three_table_join
	GROUP BY product_id
	HAVING ROUND((1 - SUM(in_price * cnt) / SUM(price * cnt))*100,1) > 24.9
  ORDER BY product_id
)tmp1