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

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

https://www.nowcoder.com/practice/65de67f666414c0e8f9a34c08d4a8ba6

select   product_id,CONCAT(profit_rate, '%')
from (
        select IFNULL(product_id,'店铺汇总') as product_id ,round((1-sum(inPrice)/sum(outPrice))*100,1) as profit_rate
        from (
            select e1.product_id,e2.price*e2.cnt as outPrice,e1.in_price*e2.cnt as inPrice
            from 
            (
            select product_id,in_price
            from tb_product_info
            where shop_id=901
            ) e1
            join 
            (
            select product_id,price,cnt
            from tb_order_detail
            where order_id in
                (select order_id
                from tb_order_overall
                where event_time>='2021-10-01' and status=1)
            ) e2
            on e1.product_id=e2.product_id
        )e3

        group by product_id
        with rollup

        HAVING profit_rate > 24.9 OR product_id IS NULL

        order by product_id

) e4

全部评论

相关推荐

点赞 评论 收藏
分享
码农索隆:单休一个月少休息4天,一年就是48天,平时节假日,别人3天假期,单休的两天
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务