Mybatis框架中SQL之一

查询innerCode字段第indexStar到indexend的字符的最大值

<select id="findOrgInCodeMax" parameterType="java.util.Map" resultMap="BaseResultMap">
select MAX(substring(innerCode,#{indexstart},#{indexend})) as innerCode,orgCode,orgName from meteor_org
</select>

更新语句

<update id="update" parameterType="org.meteor.mgr.org.entity.OrgEntity">
  update meteor_org 
  <set>
  <if test="orgCode != null">
    orgCode=#{orgCode,jdbcType=VARCHAR},
  </if>
  <if test="innerCode != null">
    innerCode=#{innerCode,jdbcType=VARCHAR},
  </if>
   </set>
   where orgId=#{orgId,jdbcType=CHAR}
</update>

去掉重复数据分为，部分字段的数据重复和所有字段的数据都重复

部分字段的值重复就要用group by 

		SELECT MAX(roleId),userId,roleMenuId,aa.menuId,aa.menuCode,aa.menuName,aa.menuUrl,aa.menuIcon,aa.parentId,aa.enableFlag FROM (
	SELECT userId,a.roleId,roleMenuId,b.menuId,c.menuCode,c.menuName,c.menuUrl,c.menuIcon,c.parentId,c.enableFlag FROM meteor_user_role AS a 
	left JOIN meteor_role_menu as b ON a.roleId=b.roleId 
LEFT JOIN meteor_role as d ON a.roleId=d.roleId
	LEFT JOIN meteor_menu as c ON c.menuId=b.menuId
	WHERE userId=#{userId,jdbcType=CHAR} and c.enableFlag='1' and d.enableFlag='1') as aa
	GROUP BY aa.menuId

所有字段的值都重复则可以用distinct关键字

比如 select   distinct name from table  ，具体可以参考https://www.cnblogs.com/shiluoliming/p/6604407.html

分页查询和根据一个值查询多个字段，把需要查询的字段写在第一个CONCAT()里面

<select id="findBysearchStr" resultMap="BaseResultMap" parameterType="org.meteor.mgr.entity.Criteria">
	select * from dlhd_contractscope where 1=1
	<if test="condition != null and condition != ''"> 
   and CONCAT(contractScopeCode,contractScopeName) LIKE CONCAT('%',#{condition},'%') 
    </if> 
     <if test="orderByClause != null" >
	      order by ${orderByClause}
	    </if>
    	<if test="mysqlOffset != null and mysqlLength != null" >
		   <![CDATA[ limit #{mysqlOffset} , #{mysqlLength} ]]>
		</if>
</select>