mybatis if-else(写法)

清疚 2022-03-26 08:16 226阅读 0赞

## mybaits 中没有else要用chose when otherwise 代替 ##

SELECT
              total.mycreatedate DATE,
              COUNT(a.seller_id) sellerNum,
              (SELECT
                COUNT(1)
              FROM
                (SELECT
                  fsr.seller_id,
                  fsr.create_time,
                  IFNULL((SELECT SUM(eo.payed) FROM erp_order eo WHERE eo.production_status = 6 AND fsr.seller_id = eo.third_merchant_id AND eo.received_time > fsr.create_time),0) transactionAmount,
                  (SELECT COUNT(g.goods_id) FROM goods g WHERE g.seller_id = fsr.seller_id AND g.marketable = 'puton') goodsNum
                FROM user_distribution ud
                  LEFT JOIN facilitator_seller_relation fsr ON ud.user_id = fsr.user_id
                  LEFT JOIN user_base_info ubi ON ud.user_id = ubi.user_id
                  WHERE fsr.disabled = 0 AND fsr.`audit_status`=1 AND ud.`apply_type`='added' AND fsr.`user_id`!=0 AND ud.`source_type`='facilitator'
                    <if test="map.parentUserId != null and map.parentUserId != ''">
                        and fsr.parent_user_id = #{map.parentUserId}
                    </if>
                    <if test="map.userId != null and map.userId != ''">
                        and fsr.user_id = #{map.userId}
                    </if>
            ) b
              WHERE b.transactionAmount >= 5
                AND b.goodsNum >= 8
                AND DATE_FORMAT(b.create_time, '%Y-%m-%d') = total.mycreatedate) activateNum,
              IFNULL(SUM(a.transactionAmount), 0) transactionAmount,
              IFNULL(SUM(a.purchaseAccount), 0) purchaseAccount
            FROM
            <choose>
                <when test="map.date != null and map.date != ''">
                    (SELECT
                    #{map.date} AS mycreatedate
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 1 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 2 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 3 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 4 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 5 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 6 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 7 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 8 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 9 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 10 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 11 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 12 DAY)
                    UNION SELECT DATE_SUB(#{map.date}, INTERVAL 13 DAY)) AS total
                </when>
                <otherwise>
                    (SELECT
                    CURDATE() AS mycreatedate
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 8 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 9 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 10 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 11 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 12 DAY)
                    UNION SELECT DATE_SUB(CURDATE(), INTERVAL 13 DAY)) AS total
                </otherwise>
            </choose>
    
              LEFT JOIN
                (SELECT
                  DATE_FORMAT(fsr.create_time, '%Y-%m-%d') DATE,
                  fsr.parent_user_id,
                  fsr.user_id,
                  fsr.seller_id,
                  (SELECT SUM(eo.payed) FROM erp_order eo WHERE eo.production_status = 6 AND fsr.seller_id = eo.third_merchant_id AND eo.received_time > fsr.create_time) transactionAmount,
                  (SELECT SUM(eo.payed) FROM pop_sys_user psu LEFT JOIN erp_order eo ON psu.mall_user_id = eo.member_id AND eo.production_status = 6 WHERE psu.seller_id = fsr.seller_id AND eo.received_time > fsr.create_time) purchaseAccount,
                  (SELECT COUNT(g.goods_id) FROM goods g WHERE g.seller_id = fsr.seller_id AND g.marketable = 'puton') goodsNum
                FROM
                  user_distribution ud
                  LEFT JOIN facilitator_seller_relation fsr ON ud.user_id = fsr.user_id
                  LEFT JOIN user_base_info ubi ON ud.user_id = ubi.user_id
                  WHERE fsr.disabled = 0 AND fsr.`audit_status`=1 AND ud.`apply_type`='added' AND fsr.`user_id`!=0 AND ud.`source_type`='facilitator'
                    <if test="map.parentUserId != null and map.parentUserId != ''">
                        and fsr.parent_user_id = #{map.parentUserId}
                    </if>
                    <if test="map.userId != null and map.userId != ''">
                        and fsr.user_id = #{map.userId}
                    </if>
               ) a
                ON a.date = total.mycreatedate
            GROUP BY total.mycreatedate
            ORDER BY total.mycreatedate