oracle 模糊查询语句 , 工作中用到的sql

最新推荐文章于 2024-06-11 17:55:55 发布

宁漂打工仔

最新推荐文章于 2024-06-11 17:55:55 发布

阅读量562

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/SpringHASh/article/details/98855564

版权

Java 专栏收录该内容

69 篇文章 0 订阅

订阅专栏

	<select id="findAll" resultMap="BaseMeterMap">
	select * from (select rownum rownum_rn, a.* from(select
		am.meterId,
		am.meterBoxId,
		am.meter_name,
		am.meter_no,
		am.house_id,
		am.phase_remark,
		am.C_DistrictBCDId,
		am.C_AddressId,
		am.C_ChannelId,
		am.CREATE_TIME,
		am.CREATE_ID,
		am.UPDATE_TIME,
		am.UPDATE_ID,
		am.DEL_FLAG,
		am.leak_channel_id,
		court.PROVINCE_ID as epuProvince,
		pro.PROVINCE_NAME_CN as epuProvinceName,
		court.CITY_ID as epuCity,
		c1.CITY_NAME_CN as epuCityName,
		court.DISTRICT_ID as epuDistrict,
		c2.CITY_NAME_CN as epuDistrictName,
		am.epu_court as epuCourt,
		court.COURT_NAME_CN as epuCourtName,
		mb.EPU_NAME as meterBoxName
	from
		t_meter am
		left join T_METERBOX mb on am.METERBOXID= mb.METERBOXID
		left join t_sy_court_info court on am.epu_court=court.COURT_ID 
		left join T_SY_PROVINCES_INFO pro on court.PROVINCE_ID = pro.PROVINCE_ID 
		left join T_SY_CITY_INFO c1 on court.CITY_ID = c1.CITY_CODE 
		left join T_SY_CITY_INFO c2 on court.DISTRICT_ID = c2.CITY_CODE
	where 1=1 and
		am.DEL_FLAG='0' and mb.DEL_FLAG='0'
		<if test="epuProvince != '' and epuProvince != null">
			and court.PROVINCE_ID = #{epuProvince,jdbcType=VARCHAR}
		</if>
		<if test="epuCity != '' and epuCity != null">
			and court.CITY_ID= #{epuCity,jdbcType=VARCHAR}
		</if>
		<if test="epuDistrict != '' and epuDistrict != null">
			and court.DISTRICT_ID= #{epuDistrict,jdbcType=VARCHAR}
		</if>
		<if test="meterNo != '' and meterNo != null">
			and LOWER(am.meter_no) like LOWER('%'||#{meterNo,jdbcType=VARCHAR}||'%')
		</if>
		<if test="meterName != '' and meterName != null">
			and LOWER(am.meter_name) like LOWER('%'||#{meterName,jdbcType=VARCHAR}||'%')
		</if>
		<if test="houseId != '' and houseId != null">
			and LOWER(am.house_id) like LOWER('%'||#{houseId,jdbcType=VARCHAR}||'%')
		</if>
		<if test="districtId != '' and districtId != null">
			and LOWER(am.C_DistrictBCDId) like LOWER('%'||#{districtId,jdbcType=VARCHAR}||'%')
		</if>
		<if test="epuCourt != '' and epuCourt != null">
			and LOWER(am.EPU_COURT) like LOWER('%'||#{epuCourt,jdbcType=VARCHAR}||'%')
		</if>
		<if test="addressId != '' and addressId != null">
			and LOWER(am.C_AddressId) like LOWER('%'||#{addressId,jdbcType=VARCHAR}||'%')
		</if>
		<if test="findContent != null and findContent !='' ">
			and (
			LOWER(am.C_DistrictBCDId) like LOWER('%'||#{findContent,jdbcType=VARCHAR}||'%')
			or LOWER(am.C_AddressId) like LOWER('%'||#{findContent,jdbcType=VARCHAR}||'%')
			or LOWER(am.C_ChannelId) like LOWER('%'||#{findContent,jdbcType=VARCHAR}||'%')
			)
		</if>
			order by am.UPDATE_TIME desc
		) a)
		<include refid="limit_sql" />
	</select>