具有象征性的sql语句

这篇文章我会一直更新，都是一些场景的sql语句

1、个人订单表，订单数据表，给定这个人的id，求这个人的所有订单记录

1.1、所有订单记录

  select o.createTime,
               o.clickTime,
               c.itemTitle,
               o.itemId,
               o.imgUrl,
               o.orderNo,
               c.shopTitle,
               o.estimateAmount,
               o.payAmount,
               c.commissionRatio,
               o.orderStatus
        from user_order o left  join
             coupon_taoke_data c on c.id = o.taokeId
        where o.userInfoId = #{userInfoId}
        

1.2、汇总记录（订单中包含有效和失效记录，销售额和收入是安卓有效记录来算的）

  select
               o.payAmount,
               o.estimateAmount,
               (IFNULL(o3.notValidOrderSize ,0 ) + IFNULL(o.orderSize ,0 ) ) as orderSize
        from user_info u
                       left join  (select    IFNULL(count(o1.orderNo),0 )  as orderSize ,
                                             IFNULL(sum(o1.estimateAmount) ,0)  as  estimateAmount ,
                                             IFNULL(sum(o1.payAmount) ,0) as payAmount,
                                             o1.userInfoId as userInfoId
                                     from    user_order o1
                                     where   o1.userInfoId = #{userInfoId} and
                                             o1.orderStatus !='订单失效'
                                  )
               o on o.userInfoId = u.id
                       left join   (select IFNULL(count(o2.orderNo),0 ) as notValidOrderSize,
                                             o2.userInfoId as userInfoId
                                      from   user_order o2
                                      where o2.orderStatus ='订单失效'  and o2.userInfoId = #{userInfoId}
                                   )
              o3 on o3.userInfoId = u.id
        where u.id = #{userInfoId};

感兴趣的，欢迎添加博主微信，

哈，博主很乐意和各路好友交流，如果满意，请打赏博主任意金额，感兴趣的在微信转账的时候，备注您的微信或者其他联系方式。添加博主微信哦。


请下方留言吧。可与博主自由讨论哦

微信	微信公众号	支付宝