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

明确题意：

计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

此处毛利率定义如下：商品毛利率=(1-进价/平均单件售价)*100%；店铺毛利率=(1-总进价成本/总销售收入)*100%。

结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

问题分解：

• 统计每个被售出的商品的售价进价（生成子表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;