关于关联查询子查询（一个参数通过从另一个表中获得）

最新推荐文章于 2023-07-25 09:15:13 发布

wanghang88

最新推荐文章于 2023-07-25 09:15:13 发布

阅读量3.2k

点赞数

本文链接：https://blog.csdn.net/wanghang88/article/details/52292376

版权

通过用户的id， user_id查询渠道表。渠道表没有这个字段，要通过user_id去别的表查询得到渠道编号。

<select id="query" parameterType="com.bill99.cbs.recon.trans.base.domain.query.ReconChannelForQuery" resultMap="reconChannelEo" resultType="java.util.List">
		SELECT
		<include refid="allColumns" />
		FROM RECONCAV.T_CBS_RECON_CHANNEL
		<where>
			<if test="userId != null">
			 CHANNEL_ID in (select CHANNEL_ID from RECONCAV.T_CBS_R_ROLE_CHANNEL where role_id in (select role_id from RECONCAV.T_CBS_R_USER_ROLE where user_id = #{userId})) 
			</if>
			<if test="channelId != null"> AND CHANNEL_ID = #{channelId} </if>
			<if test="channelNo != null"> AND CHANNEL_NO = #{channelNo}</if>
			<if test="channelName != null"> AND CHANNEL_NAME = #{channelName}</if>
			<if test="channelNameLike != null"> AND CHANNEL_NAME like '%'||#{channelNameLike}||'%'</if>
			<if test="realChannelNo != null"> AND REAL_CHANNEL_NO = #{realChannelNo}</if>
			<if test="realChannelName != null"> AND REAL_CHANNEL_NAME = #{realChannelName}</if>
			<if test="relationChannelNo != null"> AND RELATION_CHANNEL_NO = #{relationChannelNo}</if>
			<if test="reportChannel != null"> AND REPORT_CHANNEL = #{reportChannel}</if>
			<if test="bankNo != null"> AND BANK_NO = #{bankNo}</if>
			<if test="issueBankId != null"> AND ISSUE_BANK_ID = #{issueBankId}</if>
			<if test="bankName != null"> AND BANK_NAME = #{bankName}</if>
			<if test="bankAcct != null"> AND BANK_ACCT = #{bankAcct}</if>
			<if test="productNo != null"> AND PRODUCT_NO = #{productNo}</if>
			<if test="cardInterceptType != null"> AND CARD_INTERCEPT_TYPE = #{cardInterceptType}</if>
			<if test="feeSettleCycle != null"> AND FEE_SETTLE_CYCLE = #{feeSettleCycle}</if>
			<if test="reverseDesc != null"> AND REVERSE_DESC = #{reverseDesc}</if>
			<if test="subjectNo != null"> AND SUBJECT_NO = #{subjectNo}</if>
			<if test="settleModel != null"> AND SETTLE_MODEL = #{settleModel}</if>
			<if test="validityFlag != null"> AND VALIDITY_FLAG = #{validityFlag}</if>
			<if test="costSettlementType != null"> AND COST_SETTLEMENT_TYPE = #{costSettlementType}</if>
			<if test="beginCrtTime != null"> AND CRT_TIME >= #{beginCrtTime}</if>
			<if test="endCrtTime != null"> AND CRT_TIME <= #{endCrtTime}</if>
			<if test="channelNos != null and channelNos.size > 0">
				and CHANNEL_NO in 
				<foreach collection="channelNos" item="dto" open="(" separator="," close=")">
					#{dto}
				</foreach>
			</if>
		</where>
		<if test="page!=null and page.orderField != null">ORDER BY ${page.orderField} </if>
		<if test="page!=null and page.orderDirection != null">${page.orderDirection}</if>
	</select>

2）子查询：根据bankorder_id修改，而bankoeder_id在一个条件的范围内

<update id="updateByReconDate">
		update RECONCAV.T_CBS_RECON_BANKORDER_CPS t set
		t.RECON_STATUS=#{reconStatus},t.HANDLE_STATUS=#{handleStatus} ,
		t.BILLORDER_ID = null,UPD_TIME = SYSDATE
		where
		t.BANKORDER_ID  in (
		    select tt.BANKORDER_ID  from  RECONCAV.T_CBS_RECON_BILLORDER_CPS tt
		    <where>
			<if test="transType != null"> tt.SYS_TRANS_TYPE = #{transType} </if>
			<if test="reconDate != null">and tt.RECON_DATE =to_date(#{reconDate},'yyyy-mm-dd')</if>
			<if test="channelNo != null">and tt.CHANNEL_NO = #{channelNo} </if>
			</where>
			)
	 </update>