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

### 问题分解：

• 统计每个被售出的商品的售价进价（生成子表t_product_in_each_order）：
• 订单明细表内连接商品信息表：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"
• 按商品分组：GROUP BY product_id
• 加上汇总结果：WITH ROLLUP
• 商品ID列重整：IFNULL(product_id, '店铺汇总') as product_id
• 计算商品利润率：`100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)) as profit_rate`
• 保留1位小数：ROUND(x, 1)
• 筛选满足条件的分组（商品）：HAVING profit_rate > 24.9 OR product_id IS NULL
• 格式化毛利率格式：CONCAT(profit_rate, "%") as profit_rate

### 细节问题：

• 表头重命名：as
• 按商品ID排序：ORDER BY product_id

### 完整代码：

``````SELECT product_id, CONCAT(profit_rate, "%") as profit_rate
FROM (
SELECT IFNULL(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;
``````