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

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

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

SELECT
    '店铺汇总' AS product_id ,
    CONCAT(ROUND(100*(1-SUM(in_price*cnt)/SUM(price*cnt)),1),'%') AS profit_rate
FROM tb_order_detail detail 
JOIN tb_order_overall overall USING(order_id)
JOIN tb_product_info info USING(product_id)
WHERE DATE_FORMAT(overall.event_time,'%Y-%m')>='2021-10' AND shop_id=901
UNION
SELECT *
FROM(
    SELECT 
    product_id,
    CONCAT(ROUND(100*(1-SUM(in_price*cnt)/SUM(price*cnt)),1),'%') AS profit_rate
FROM tb_order_detail detail
JOIN tb_order_overall overall USING(order_id)
JOIN tb_product_info info USING(product_id)
WHERE shop_id=901 AND DATE_FORMAT(overall.event_time,'%Y-%m')>='2021-10'
GROUP BY product_id
HAVING SUBSTR(profit_rate,1,4)>24.9
ORDER BY profit_rate
) c

注意SQL的执行顺序即可





全部评论

相关推荐

不愿透露姓名的神秘牛友
07-07 14:00
不想多说了,什么逆天HR,还要教我礼貌😂
机械打工仔:这不纯傻卵吗,他还操心上别人老板了
投递BOSS直聘等公司7个岗位
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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