具有象征性的sql语句

╰+哭是因爲堅強的太久メ 2022-04-04 17:52 164阅读 0赞

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

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

![WX20181218-105250@2x][WX20181218-105250_2x]

#### 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};

![ContactAuthor][]

[WX20181218-105250_2x]: /images/20220404/5e9bec3a9fa048d8ab173084ccc0fbca.png
[ContactAuthor]: /images/20220404/3ad8bb81445f48e1bb4317c333990943.png