题解 | #某店铺的各商品毛利率及店铺整体毛利率#
某店铺的各商品毛利率及店铺整体毛利率
https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6
按照题目给出的商品毛利率公式为：商品毛利率=(1-进价/平均单件售价)*100%
以商品8001为例，8001售出了3件，销售总额为25500，进价总额为18000，平均单件售价为8500，毛利率应该为（1-18000/8500）%吧，这不是跟解释那里不一样吗（笑哭），难道是我理解错了吗（沉思....）
解题步骤：
①首先建立t1表，表中包含公式计算所需要的列和筛选条件即
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"
WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"
②对t1表使用公式，并且汇总数据（默认升序，则null最小排第一行），加上筛选条件
ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) 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")t1
GROUP BY product_id WITH ROLLUP
SELECT product_id,
ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) 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")t1
GROUP BY product_id WITH ROLLUP
得到
③重命名NULL，
SELECT IFNULL(productid,'店铺汇总')product_id,
ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) 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")t1
GROUP BY product_id WITH ROLLUP
ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) 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")t1
GROUP BY product_id WITH ROLLUP
HAVING profit_rate > 24.9 OR product_id IS NULL
ORDER BY product_id
得到
④最后在外圈再包一层%
SELECT product_id, CONCAT(profit_rate, "%") as profit_rate
FROM (
SELECT IFNULL(product_id, '店铺汇总') 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 = 901and DATE(event_time) >= "2021-10-01"
) t1
GROUP BY product_id
WITH ROLLUP
HAVING profit_rate > 24.9 OR product_id IS NULL
ORDER BY product_id
) t2
我在mysql里面运行，发现不管在order by后面加上asc或者desc都无法使店铺汇总排在第一行，不知道怎么回事。而且如果去掉 or product_id is null也不会使结果发生改变。
另外with rollup 包含了公式，也就是说对结果汇总是根据公式进行汇总的，而不是两个商品毛利率的简单相加。
投递众安保险等公司10个岗位 >