题解 | #连续签到领金币#

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" and status=1
    ) as t_product_in_each_order
    GROUP BY product_id
    WITH ROLLUP
    HAVING (1 - SUM(in_price*cnt) / SUM(price*cnt)) > 0.249&nbs***bsp;product_id IS NULL
    ORDER BY product_id
) as t1;