mybatis sql一些写法记录

<!--计算销售所有的销售业绩金额-->
    <select id="getAllPerformance" parameterType="java.util.Map" resultType="double">
        select IFNULL(SUM(figure),0) from aniucrm_salesman_performance_pool
        where join_time BETWEEN #{startTime} AND #{endTime}
        AND status = 1 AND employee_id IN
        <foreach item="list" index="index" collection="list" open="(" separator="," close=")">
            #{list}
        </foreach>
    </select>


    <insert id="addSalesmanPerformancePoolList"  parameterType="java.util.List">
        insert into aniucrm_salesman_performance_pool
        (code,employee_id,divide_percent,order_payment_number,order_number,customer_id,product_id,
        figure,join_time,memo,create_time,create_person)
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (#{item.code},#{item.employeeId},#{item.dividePercent},#{item.orderPaymentNumber},
            #{item.orderNumber},#{item.customerId},#{item.productId},#{item.figure},
            #{item.joinTime},#{item.memo},CURRENT_TIMESTAMP,#{item.createPerson})
        </foreach>
    </insert>

<update id="updateHostSettleBySettlleUuid" parameterType="java.util.List" >
    update aniu_host_prg_perf_red_packet set is_settle = 1,update_time = NOW() where settle_uuid IN
    ( <foreach collection="list" item="item" index="index" separator="," >
    #{item}
  </foreach>
    )
  </update>

 

//Mysql 在 mybatis 环境下 批量 存在则更新，不存在则插入

<INSERT id="insertBatch" parameterType="com.gcj.entity.ipp.IppInfoPrice">
    INSERT INTO ipp_info_price (ipp_package_id, ipp_resource_id, 
       info_price, created_at,ipp_region_term_id,YEAR
      )
    VALUES
    <foreach collection="list" item="item" index="index" separator="," >  
       (#{item.ippPackageId,jdbcType=INTEGER}, #{item.ippResourceId,jdbcType=INTEGER}, 
       #{item.infoPrice,jdbcType=DECIMAL}, #{item.createdAt,jdbcType=TIMESTAMP},#{item.ippRegionTermId},#{item.year})
    </foreach>
     ON DUPLICATE KEY UPDATE
        info_price = VALUES(info_price)
  </INSERT>

//批量删除
<delete id="batchDelByMd5AndDataRange" parameterType="java.util.List">
    <foreach collection="list" item="item" separator=";">
      delete from video_ads_data
      where video_md5 = #{item.videoMd5} and data_range = #{item.dataRange}
    </foreach>
  </delete>