mysql - ＜choose＞多条件判断 (等同于switch,case,default)

使用介绍

choose 标签作用是通过条件判断来拼接 SQL 语句，类似于 Java 中的 switch 语句，从上到下，当有匹配的条件时，跳出 choose 语句；如果所有条件都不成立则执行 otherwise 标签中的内容

注意最先需要判断的条件要放在前面，当匹配到某个条件时，就不会判断后面的语句了。

语法

<choose>
	<when test=条件1>
		...
	</when>
	<when test=条件2>
		...
	</when>
	<when test=条件3>
		...
	</when>
	...
	<otherwise>
		...
	</otherwise>
</choose>

<select id="selectDistributeUserIds" resultType="java.lang.Integer">
	select u.id from users u
	<where>
		u.belongDistributorUserId is not null
		<if test="registerStart!=null">and #{registerStart} &lt;= u.createdAt</if>
		<if test="registerEnd!=null">and #{registerEnd} >= u.createdAt</if>
		<if test="id!=null">and u.id=#{id}</if>
		<if test="status!=null">and u.status=#{status}</if>
		<if test="username!=null and username!=''">and u.username like concat('%',#{username},'%')</if>
		<if test="email!=null and email!=''">and u.email like concat('%',#{email},'%')</if>
		<if test="phone!=null and phone!=''">and u.phone like concat('%',#{phone},'%')</if>
		<if test="remark!=null and remark!=''">and u.remark like concat('%',#{remark},'%')</if>
		<if test="distributorUrl!=null and distributorUrl!=''">and u.distributorUrl =#{distributorUrl}</if>
		<if test="belongCashoutStatus!=null">and u.belongCashoutStatus=#{belongCashoutStatus}</if>
		<choose>
			<when test="distributorLevel!=null">
				<if test="distributorLevel==1">
					<if test="belongFirstUserId!=null">and u.belongFirstUserId=#{belongFirstUserId} and u.belongSecondUserId is not null</if>
				</if>
				<if test="distributorLevel==2">
					<if test="belongSecondUserId!=null">and u.belongSecondUserId=#{belongSecondUserId} and u.belongThirdUserId is not null</if>
				</if>
			</when>
			<otherwise>
				<if test="belongFirstUserId!=null">and u.belongFirstUserId=#{belongFirstUserId}</if>
				<if test="belongSecondUserId!=null">and u.belongSecondUserId=#{belongSecondUserId}</if>
				<if test="belongThirdUserId!=null">and u.belongThirdUserId=#{belongThirdUserId}</if>
			</otherwise>
		</choose>
	</where>
	order by u.createdAt desc
</select>