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

WITH t_1 AS (
    SELECT
        a.product_id,
        a.price,
        a.cnt,
        c.shop_id,
        c.in_price
    FROM tb_order_detail a
    INNER JOIN tb_order_overall b ON a.order_id = b.order_id
    INNER JOIN tb_product_info c ON a.product_id = c.product_id
    WHERE b.event_time>='2021-10-01'
    AND c.shop_id = 901
    AND b.status != 2
),
t_2 AS (
    SELECT
        product_id,
        SUM( in_price * cnt) AS in_price_cnt,
        SUM( price * cnt) AS price_cnt,
        ROUND((1 - SUM( in_price * cnt) / SUM(price * cnt)) * 100,1)
        AS profit_rate
    FROM t_1
    GROUP BY product_id
    HAVING profit_rate > 24.9
    ORDER BY product_id ASC
),
t_3 AS (
    SELECT product_id,
           CONCAT(profit_rate,'%') AS profit_rate
    FROM t_2
),
t_4 AS (
    SELECT
    '店铺汇总' AS product_id,
    CONCAT(
    ROUND(
        (1 -  SUM( in_price * cnt) / SUM(price * cnt)) * 100
        ,1)
        ,'%') AS profit_rate
    FROM t_1
)
SELECT * FROM t_4
UNION ALL
SELECT * FROM t_3;

# 这道题的关键在于，b.event_time>='2021-10-01'，虽然字段是datetime类型的分秒类型了。