两个mybatis下的sql例子

一、根据id不完全删除（删除一部分保留一部分）

  <delete id="delete" parameterType="String">
		UPDATE SYS_DEPT
		SET 
			DELETE_FLAG = '1',
			UPDATE_TIME = sysdate
		WHERE DELETE_FLAG = '0'
		<foreach collection="array" index="index" item="item" open="" separator="" close="">  
			and ID NOT IN(select du.dept_id from SYS_USER u,SYS_DEPT_USER du where u.id = du.user_id and u.delete_flag = 0 and du.dept_id in (#{item}) group by du.dept_id)
   		</foreach>
   		
   		<foreach collection="array" index="index" item="item" open="" separator="" close="">  
			and ID NOT IN(select d.parent_id from SYS_DEPT d where d.delete_flag = 0 and d.parent_id in (#{item}) group by d.parent_id)
   		</foreach>
   		
			AND ID IN 
		<foreach collection="array" index="index" item="item" open="(" separator="," close=")">  
            #{item}
   		</foreach>
	</delete>

注： #{item}为一个数组参数

二、分别根据不同条件查数据，获得一个list，元素为每个查询的结果条目数，多用于校验

  <!-- 校验中文名和英文名是否存在 返回长度为2的list -->
	<select id="getMenuCnameAndEnameCount" parameterType="com.ebon.platform.menu.vo.SysMenu"
		resultType="Integer">
		SELECT
		COUNT(ID)
		FROM SYS_MENU M
		WHERE M.CNAME = #{cname}
		AND M.PARENT_ID =
		#{parentId}
		AND M.DELETE_FLAG = 0
		<if test="null!=id and id!=''">
			AND M.ID <![CDATA[<]]> <![CDATA[>]]>
			#{id}
		</if>
		UNION ALL
		SELECT
		COUNT(ID)
		FROM SYS_MENU M
		WHERE M.ENAME = #{ename}
		AND M.PARENT_ID =
		#{parentId}
		AND M.DELETE_FLAG = 0
		<if test="null!=id and id!=''">
			AND M.ID <![CDATA[<]]> <![CDATA[>]]>
			#{id}
		</if>
	</select>

注：不等于<>，在mybatis中需要转义 用<![CDATA[<]]> 分别对大于和小于转义