MyBatis-foreach

最新推荐文章于 2024-05-16 10:11:18 发布

just_in_time

最新推荐文章于 2024-05-16 10:11:18 发布

阅读量99

点赞数

CC 4.0 BY-SA版权

文章标签： MyBatis foreach

本文链接：https://blog.youkuaiyun.com/just_in_time/article/details/84323929

本文深入探讨了SQL查询中使用distinct关键字的优化策略，以及如何通过灵活运用IF语句处理不同条件组合来提高查询效率。重点分析了平台ID、账号ID、角色ID、组ID、扩展属性和限制条件在查询中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

	<select id="list" parameterType="domain.AccountQuery" resultMap="baseResult">
		SELECT distinct a.id,a.accountId,a.password,a.status,a.platformId,a.createDate,a.lastModifyDate,a.lastLoginTime
		 FROM T_AUTH_ACCOUNT a,T_AUTH_ACCOUNT_EXTENSION e  
		where a.accountId=e.accountId and a.platformId=#{platformId}

		<if test="accountId != null">
		    AND a.accountId like '%' || #{accountId} || '%'
		</if>
		<if test="roleId != null and groupId == null">
		    AND a.id in (select accountId from T_TUAH_ACCOUNT_ROLE r where r.roleId=#{roleId})
		</if>
		<if test="groupId != null and roleId == null">
		    AND a.id in (select accountId from T_AUTH_ACCOUNT_GROUP g where g.groupId=#{groupId})
		</if>
		<if test="roleId != null and groupId != null">
		    AND a.id in (select r.accountId from T_AUTH_ACCOUNT_GROUP g,T_TUAH_ACCOUNT_ROLE r 
		    				 where r.accountId=g.accountId and r.roleId=#{roleId} and g.groupId=#{groupId})
		</if>
		<if test="accountExts != null">
			AND a.accountId in( select accountId from 
			<foreach collection="accountExts" item="ext" separator=" join " index="index">
			    (select accountId from T_AUTH_ACCOUNT_EXTENSION e1 where
			    e1.propBTypeId = #{ext.propBTypeId} AND e1.propSTypeCode = #{ext.propSTypeCode} AND e1.propValue=#{ext.propValue,jdbcType=VARCHAR}) t${index}
			    <if test="index!=0">
			        using(accountId)
			    </if>
			</foreach>)
		</if>
		<if test="limit != null">
			LIMIT #{limit}
		</if>
		<if test="offset != null">
			OFFSET #{offset}
		</if>
	</select>