mysql 8.0 cte查询以及批量操作

1.批量插入

<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO
sal_basic_scope (id, property_id, sub_id,
user_tableinfo, description, is_delete)
values
<foreach item="item" index="index" collection="list" separator=",">
(#{item.id,jdbcType=VARCHAR}, #{item.propertyId,jdbcType=VARCHAR}, #{item.subId,jdbcType=VARCHAR},
#{item.userTableinfo,jdbcType=VARCHAR}, #{item.description,jdbcType=VARCHAR}, #{item.isDelete,jdbcType=VARCHAR}
)
</foreach>
</insert>

2.批量更新

<!--   批量更新数据 -->
<update id="batchUpdate" parameterType="java.util.List">
update STA_RECEPTION_MEMBER
<trim prefix="set" suffixOverrides=",">
 <trim prefix="OFFICE_ID =case" suffix="end,">  
     <foreach collection="list" item="cus">  
         <if test="cus.officeId!=null">  
             when RECEPTION_ID=#{cus.receptionId} then #{cus.officeId}  
         </if>  
     </foreach>  
 </trim>
  
 
</trim>  
<where>  
    <foreach collection="list" separator="or" item="cus">  
        RECEPTION_ID = #{cus.receptionId}  
    </foreach>  
</where>  

</update>

3.批量删除

<delete id="batchDelete" parameterType="java.util.List">
delete from t_kq_shift_month
where id in
<foreach collection="list" item="item"  open="(" close=")" separator=",">
#{item.id}
</foreach>
</delete>

4.cte根据某个条件联合查询（自关联查询）

WITH RECURSIVE test(id, title, type,parent_id)
                     AS
                     (

                       SELECT DISTINCT id, title,type,parent_id
                       FROM limis_template WHERE title like  CONCAT('%', #{title}, '%') and  type =#{type} and deleted=0
                       UNION ALL
                       SELECT e.id, e.title,e.type,e.parent_id
                       FROM test AS ep JOIN limis_template AS e  ON ep.parent_id = e.id
                     )SELECT * FROM test;