MySQL 插入一张表的部分数据到另一张表

  2. # 导入账户数据 - 总店id需要手动补全
  3. insert into pro_user_account (id, store_id, branch_id, user_name, password, real_name, 
  4.                               status, create_user, update_user,
  5.                               create_time, update_time)
  6. SELECT user_id,
  7.        '0',
  8.        IFNULL(sup_user_id,'0'),
  9.        user_name,
  10.        passwd,
  11.        real_name,
  12.        status,
  13.        null,
  14.        null,
  15.        create_time,
  16.        update_time
  17. from pro_user_info as a
  18. where status = 0
  19.   and passwd is not null;
  1. `Mybatis` 数组查询
  3. ```xml
  4. <foreach collection="list" item="employeeId" index="index" open="(" close=")" separator=",">
  5.     #{employeeId}
  6. </foreach>
  8. <foreach collection="array" item="employeeId" index="index"
  9.             open="(" close=")" separator=",">
  10.             #{employeeId}
  11. </foreach>
  13. 根据数组批量查询
  14. List<Privilege> selectPrivilegeByIds(@Param("privilegeIds") Integer[] privilegeIds);
  16. <select id="selectPrivilegeByIds" resultMap="BaseResultMap" >
  17.  select
  18.   <include refid="Base_Column_List" />
  19.   from diary_privilege
  20.  where id in
  21.   <foreach collection="array" item="item" index="index" open="(" separator="," close=")">
  22.               #{item}
  23.    </foreach>
  24. </select>
  25. 根据集合插入批量插入
  27. int insertEmployeeRole(EmployeeRoleVo employeeRole);（EmployeeRoleVo中包含List<Role> roleList）
  28.   <insert id="insertEmployeeRole" parameterType="com.jimmy.demo.vo.EmployeeRoleVo" >
  29.   insert into diary_employee_role (employeeId,roleId)
  30.   values
  31.   <foreach collection="roleList" item="item" index="index" separator="," >  
  32.       (#{employee.eid},#{item.id})  
  33.   </foreach>  
  34. </insert>
  3. <a name="3ymrdx"></a>
  4. ### 根据List<Object> 批量删除
  5. ```sql
  6. <delete id="deleteBatchByCondition">
  7.     delete from BOM_PART_PARTS_P A
  8.     where exists
  9.     (
  10.     select 1 from(
  11.     <foreach collection="list" item="item" index="index" separator="union all">
  12.         select  B.PART_PARTS_CODE from BOM_PART_PARTS_P B where 1=1
  13.         and B.PART_CODE = #{item.partCode,jdbcType=VARCHAR}
  14.         and B.PARENT_PART_CODE = #{item.parentPartCode,jdbcType=VARCHAR}
  15.         and B.LINE_NO = #{item.lineNo,jdbcType=VARCHAR}
  16.         and B.ACTIVATE = 0
  17.     </foreach>
  18.     )S where  A.PART_PARTS_CODE = S.PART_PARTS_CODE
  19.     )
  20. </delete>