SQL批量操作大全

1.list嵌套list查询SQL
  <!--组合嵌套查询-->
    <resultMap id="queryOrderGoodsOD" type="com.mengniu.vo.OrderVO">
        <id column="orderId" property="orderId" jdbcType="INTEGER"/>
        <result column="orderNumber" property="orderNumber" jdbcType="VARCHAR"/>
        <result column="orderDate" property="orderDate" jdbcType="TIMESTAMP"/>
        <result column="orderStatus" property="orderStatus" jdbcType="VARCHAR"/>
        <result column="totalQuantity" property="totalQuantity" jdbcType="INTEGER"/>
        <result column="totalActualPay" property="totalActualPay" jdbcType="VARCHAR"/>
        <collection property="goods" javaType="java.util.ArrayList" select="com.mengniu.mapper.ConsumerMapper.queryGoodList"
                     ofType = "com.mengniu.vo.GoodsVO" column="{orderId=orderId,totalActualPay=totalActualPay}">
            <result column="name" property="name" jdbcType="VARCHAR"/>
            <result column="size" property="size" jdbcType="INTEGER"/>
            <result column="url" property="url" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>
 <!--订单列表查询商品信息-->
    <select id="queryGoodList" resultType="com.mengniu.vo.GoodsVO">
        SELECT
            top.name name,
            top.num size,
            top.mainUrl url
        FROM
            t_order_product top
        WHERE
            top.orderId = #{orderId}
   </select>

 <!--订单分页查询列表-->
    <select id="queryOrderPageList" resultMap="queryOrderGoodsOD">
        SELECT
            too.id orderId,
            too.orderNumber, -- 订单编号
            too.createdAt orderDate, -- 订单时间
            too.status orderStatus, -- 订单状态
            too.totalQuantity totalQuantity, -- 购物订单总数
            too.payAmount totalActualpay -- 购物订单实际支付金额
        FROM
            t_ordinary_order too
        <where>
            too.cusId = #{customerId}
            <if test="dto.type != null">
                and too.status = #{dto.type}
            </if>
        </where>
        <if test="dto.start != null and dto.pageSize != null">
            LIMIT #{dto.start},#{dto.pageSize}
        </if>
    </select>

2.批量新增SQL
   <insert>
       INSERT INTO
            dpo_personnel_project_summary_report ( id, staff_id, staff_name, staff_depart_name, pteaioi_type, staff_rank, user_status, user_skill, base_address, work_address, create_time )
        <foreach collection="list" item="obj" open="VALUES" close=";" separator=",">
            ( #{obj.id}, #{obj.staffId}, #{obj.staffName}, #{obj.staffDepartName}, #{obj.pteaioiType}, #{obj.staffRank}, #{obj.userStatus}, #{obj.userSkill}, #{obj.baseAddress}, #{obj.workAddress}, #{obj.createTime} )
        </foreach>
 </insert>

3.批量编辑SQL
入参：List<QueryDeviceVO> list
<update id="batchUpdateOrder" parameterType="java.util.List" >
        update t_device
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="device_longitude =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.deviceLongitude!=null">
                        when device_code=#{i.deviceCode} then #{i.deviceLongitude}
                    </if>
                </foreach>
            </trim>
            <trim prefix="device_latitude =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.deviceLatitude!=null">
                        when device_code=#{i.deviceCode} then #{i.deviceLatitude}
                    </if>
                </foreach>
            </trim>
            <trim prefix="device_address =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.deviceAddress!=null">
                        when device_code=#{i.deviceCode} then #{i.deviceAddress}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="i" index="index" >
            device_code=#{i.deviceCode}
        </foreach>
 </update>