部分sql语句

参考
http://blog.csdn.net/luqin1988/article/details/7831927

方式1、修改

	<select id="getExchangeRecordsInPeriod" resultMap="exchangeRecordVOResultMap">
		SELECT ter.*, tu.nick_name FROM t_exchange_record ter left join t_user
		tu on ter.fk_anchor = tu.fk_origin_user
		WHERE
		1=1
		<if test="userId > 0">
			and fk_anchor = #{userId}
		</if>
		<if test="status >= 0">
			and state = #{status}
		</if>
		<if test="start != null and end != null">
			and create_date between #{start} and #{end}
		</if>
		order by create_date desc
		limit #{offset}, #{limit}
	</select>

	<!-- count -->
	<select id="countExchangesInPeriod" resultType="int">
		SELECT count(1)
		FROM t_exchange_record
		WHERE 1=1
		<if test="userId > 0">
			and fk_anchor = #{userId}
		</if>
		<if test="status >= 0">
			and state = #{status}
		</if>
		<if test="start != null and end != null">
			and create_date between #{start} and #{end}
		</if>
	</select>

方式2

<sql id="anchorViewForPage">
        and a.fk_manager_id in <foreach item="item" index="index" collection="search.managerList" open="(" separator="," close=")">#{item}</foreach>

        <if test="search.nickName !=''">
            and t.nick_name like CONCAT('%',#{search.nickName},'%')
        </if>
        <if test="search.managerId > -1">
            and a.fk_manager_id = #{search.managerId}
        </if>
        <if test="search.userStatus>-1">
            AND t.user_status = #{search.userStatus}
        </if>
        <if test="search.anchorType>-1">
            AND a.fk_anchor_type = #{search.anchorType}
        </if>
        <if test="search.proxy>-1">
            AND a.fk_proxy_code = #{search.proxy}
        </if>
        <if test="search.roomId>-1">
            AND room.pk_room = #{search.roomId}
        </if>
        <if test="search.bank > -1">
            <choose>
                <when test="search.bank == 1">
                    AND ( a.bank IS NULL
                    OR a.bank = ""
                    OR a.deposit_bank IS NULL
                    OR a.deposit_bank = ""
                    OR a.deposit_bank_address IS NULL
                    OR a.deposit_bank_address = ""
                    OR a.bank_card_number IS NULL
                    OR a.id_number IS NULL
                    OR a.bank_account_holder IS NULL)
                </when>
                <when test="search.bank == 0">
                    AND ( a.bank IS NOT NULL
                    AND a.bank != ""
                    AND a.deposit_bank IS NOT NULL
                    AND a.deposit_bank != ""
                    AND a.deposit_bank_address IS NOT NULL
                    AND a.deposit_bank_address != ""
                    AND a.bank_card_number IS NOT NULL
                    AND a.id_number IS NOT NULL
                    AND a.bank_account_holder IS NOT NULL
                    AND a.bank_account_holder != "")
                </when>
            </choose>
        </if>
    </sql>

    <select id="getAnchorViewListByPage" resultMap="ddshowUserWithAnchorDetail">
        <![CDATA[
        SELECT a.*,
        t.nick_name  as nick_name,
        t.email,
        t.mobile,
        t.fk_origin_user,
        t.user_status
         FROM t_user AS t
	    	    LEFT JOIN t_anchor_detail AS a ON t.fk_origin_user = a.fk_user
            left join t_room room on t.fk_origin_user=room.fk_user
                WHERE t.is_anchor = 1
	    ]]>
        <include refid="anchorViewForPage"/>
        group by room.fk_user LIMIT #{offset},#{limit}
    </select>