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

思路:

第一步: 查出来有用的信息

-- JOIN ...USING(字段) 代表内连接 using(字段) 代表使用这个 字段连接两个表
SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        JOIN tb_product_info USING(product_id)
        JOIN tb_order_overall USING(order_id)
        WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"

运行结果如下:

+------------+-------+-----+----------+
| product_id | price | cnt | in_price |
+------------+-------+-----+----------+
|       8001 |  8500 |   2 |     6000 |
|       8001 |  8500 |   1 |     6000 |
|       8003 | 18000 |   1 |    12000 |
+------------+-------+-----+----------+
3 rows in set (0.00 sec)

第二步: 使用 with rollup 计算总和

-- 总和计算出来在 profit_rate列显示，但是product_id 会为null，可以使用 ifnull() 函数，也可以使用 coalesce() 函数，将 product_id 填充
SELECT coalesce(product_id, '店铺汇总') as product_id,
        ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) as profit_rate
    FROM (
        SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        JOIN tb_product_info USING(product_id)
        JOIN tb_order_overall USING(order_id)
        WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"
    ) as t_product_in_each_order
    GROUP BY product_id
    WITH ROLLUP
    HAVING profit_rate > 24.9 OR product_id IS NULL
    ORDER BY product_id

运行结果:

+--------------+-------------+
| product_id   | profit_rate |
+--------------+-------------+
| 店铺汇总     |        31.0 |
| 8001         |        29.4 |
| 8003         |        33.3 |
+--------------+-------------+
3 rows in set, 2 warnings (0.00 sec)

第三步: 使用% 连接

SELECT product_id, CONCAT(profit_rate, "%") as profit_rate
FROM (
    SELECT coalesce(product_id, '店铺汇总') as product_id,
        ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) as profit_rate
    FROM (
        SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        JOIN tb_product_info USING(product_id)
        JOIN tb_order_overall USING(order_id)
        WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"
    ) as t_product_in_each_order
    GROUP BY product_id
    WITH ROLLUP
    HAVING profit_rate > 24.9 OR product_id IS NULL
    ORDER BY product_id
) as t1;