条件sql ibatis
<!-- 多条件查询 -->
<select id="MS-CUSTOM-PANDECT-INFO-BY-CONDITIONS" resultMap="RM-CUSTOM-PANDECT-INFO-ORDER">
select t.*, d.real_name sale_name from
(SELECT custom_id ,real_name ,user_name ,type ,account_name ,under_investor ,real_name_authentication ,address ,gender ,age ,referees ,register_time ,mobile ,cert_no ,mail ,qq ,account_remainder ,multiple_rank ,active_extent ,invest_frequency ,lately_invest_time ,invest_amount ,lifecycle ,bank_cards ,platform_id FROM custom_pandect_info p
where 1=1
<dynamic>
<isNotEmpty property="customPandectInfo.realName" prepend=" and " >
real_name=#customPandectInfo.realName#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.userName" prepend=" and " >
user_name=#customPandectInfo.userName#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.type" prepend=" and " >
type=#customPandectInfo.type#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.realNameAuthentication" prepend=" and " >
real_name_authentication=#customPandectInfo.realNameAuthentication#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.address" prepend=" and " >
address=#customPandectInfo.address#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.sex" prepend=" and " >
gender=#customPandectInfo.sex#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.multipleRank" prepend=" and " >
multiple_rank=#customPandectInfo.multipleRank#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.activeExtent" prepend=" and " >
active_extent=#customPandectInfo.activeExtent#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.lifecycle" prepend=" and " >
lifecycle=#customPandectInfo.lifecycle#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.ageGroup" prepend=" and " >
age_level=#customPandectInfo.ageGroup#
</isNotEmpty>
<isEqual property="customPandectInfo.accountRemain" prepend=" and " compareValue="有">
<![CDATA[
account_remainder>0
]]>
</isEqual>
<isEqual property="customPandectInfo.accountRemain" prepend=" and " compareValue="无">
<![CDATA[
account_remainder=0
]]>
</isEqual>
<isNotEmpty property="customPandectInfo.saleName" prepend=" and ">
EXISTS (SELECT uu.custom_id ,ui.* from user_custom uu
inner JOIN user_info ui on ui.id=uu.user_id and ui.STATUS='1' and uu.status='1'
and ui.real_name LIKE #customPandectInfo.saleName#
where uu.custom_id= p.custom_id )
</isNotEmpty>
<isNotEmpty property="customPandectInfo.checkedTrades" prepend=" and ">
exists(select vest.custom_id from custom_invest_info vest
where vest.custom_id=p.custom_id and vest.trade_id
<iterate property="ids" close="))" open="in(" conjunction=",">
<![CDATA[
#customPandectInfo.checkedTrades[]#
]]>
</iterate>
</isNotEmpty>
</dynamic>
<isNotNull property="customPandectInfo.orderBy">
order by #customPandectInfo.orderBy#
</isNotNull>
LIMIT #limitStart#,#pageSize# ) t
left join user_custom c on (c.custom_id=t.custom_id and c.status='1')
left join user_info d on (d.id=c.user_id and d.status='1')
</select>
<!--查询满足条件的所有 数据 -->
<select id="MS-CUSTOM-PANDECT-INFO-BY-COUNT" resultClass="long">
SELECT count(*) FROM custom_pandect_info p
where 1=1
<dynamic>
<isNotEmpty property="customPandectInfo.realName" prepend=" and " >
real_name=#customPandectInfo.realName#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.userName" prepend=" and " >
user_name=#customPandectInfo.userName#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.type" prepend=" and " >
type=#customPandectInfo.type#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.realNameAuthentication" prepend=" and " >
real_name_authentication=#customPandectInfo.realNameAuthentication#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.address" prepend=" and " >
address=#customPandectInfo.address#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.sex" prepend=" and " >
gender=#customPandectInfo.sex#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.multipleRank" prepend=" and " >
multiple_rank=#customPandectInfo.multipleRank#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.activeExtent" prepend=" and " >
active_extent=#customPandectInfo.activeExtent#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.lifecycle" prepend=" and " >
lifecycle=#customPandectInfo.lifecycle#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.ageGroup" prepend=" and " >
age_level=#customPandectInfo.ageGroup#
</isNotEmpty>
<isEqual property="customPandectInfo.accountRemain" prepend=" and " compareValue="有">
<![CDATA[
account_remainder>0
]]>
</isEqual>
<isEqual property="customPandectInfo.accountRemain" prepend=" and " compareValue="无">
<![CDATA[
account_remainder=0
]]>
</isEqual>
<isNotEmpty property="customPandectInfo.saleName" prepend=" and ">
EXISTS (SELECT uu.custom_id ,ui.* from user_custom uu
inner JOIN user_info ui on ui.id=uu.user_id and ui.STATUS='1' and uu.status='1'
and ui.real_name LIKE #customPandectInfo.saleName#
where uu.custom_id= p.custom_id )
</isNotEmpty>
<isNotEmpty property="customPandectInfo.checkedTrades" prepend=" and ">
exists(select vest.custom_id from custom_invest_info vest
where vest.custom_id=p.custom_id and vest.trade_id
<iterate property="ids" close="))" open="in(" conjunction=",">
<![CDATA[
#customPandectInfo.checkedTrades[]#
]]>
</iterate>
</isNotEmpty>
</dynamic>
</select>
公用公共条件
<!--客户列表所有查询条件 -->
<sql id="MS-CUSTOM-PANDECT-INFO-QUERY-WHERE">
<dynamic prepend="WHERE">
<isNotEmpty property="customPandectInfo.realName" prepend=" and ">
real_name=#customPandectInfo.realName#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.userName" prepend=" and ">
user_name=#customPandectInfo.userName#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.type" prepend=" and ">
type=#customPandectInfo.type#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.realNameAuthentication"
prepend=" and ">
real_name_authentication=#customPandectInfo.realNameAuthentication#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.address" prepend=" and ">
address=#customPandectInfo.address#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.sex" prepend=" and ">
gender=#customPandectInfo.sex#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.multipleRank"
prepend=" and ">
multiple_rank=#customPandectInfo.multipleRank#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.activeExtent"
prepend=" and ">
active_extent=#customPandectInfo.activeExtent#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.lifecycle"
prepend=" and ">
lifecycle=#customPandectInfo.lifecycle#
</isNotEmpty>
<isNotEmpty property="customPandectInfo.ageGroup" prepend=" and ">
age_level=#customPandectInfo.ageGroup#
</isNotEmpty>
<isEqual property="customPandectInfo.accountRemain" prepend=" and "
compareValue="有">
<![CDATA[
account_remainder>0
]]>
</isEqual>
<isEqual property="customPandectInfo.accountRemain" prepend=" and "
compareValue="无">
<![CDATA[
account_remainder=0
]]>
</isEqual>
<isNotEmpty property="customPandectInfo.userId" prepend=" and">
exists (select uuu.custom_id from user_custom uuu
where uuu.status='1' and uuu.user_id=#customPandectInfo.userId#
and uuu.custom_id=p.custom_id)
</isNotEmpty>
<isNotEmpty property="customPandectInfo.saleName" prepend=" and ">
EXISTS (SELECT uu.custom_id from user_custom uu
inner JOIN user_info ui on ui.id=uu.user_id and ui.STATUS='1' and
uu.status='1'
and ui.real_name LIKE #customPandectInfo.saleName#
where uu.custom_id= p.custom_id )
</isNotEmpty>
<isNotEmpty property="customPandectInfo.ids" prepend=" and ">
exists(select vest.custom_id from custom_invest_info vest
where vest.custom_id=p.custom_id and vest.trade_id
<iterate property="customPandectInfo.ids" close="))" open="in("
conjunction=",">
<![CDATA[
#customPandectInfo.ids[]#
]]>
</iterate>
</isNotEmpty>
<isNotNull property="customPandectInfo.status" prepend=" and">
exists(select cci.custom_id from custom_communicate_info cci where
cci.custom_id=p.custom_id and
cci.commu_status=#customPandectInfo.status#
)
</isNotNull>
</dynamic>
</sql>
<!-- 多条件查询 -->
<select id="MS-CUSTOM-PANDECT-INFO-BY-CONDITIONS" resultMap="RM-CUSTOM-PANDECT-INFO-ORDER">
<![CDATA[ select t.*, d.real_name sale_name from
(SELECT custom_id ,real_name ,user_name ,type ,account_name ,under_investor ,real_name_authentication ,address ,gender ,age ,referees ,register_time ,mobile ,cert_no ,mail ,qq ,account_remainder ,multiple_rank ,active_extent ,invest_frequency ,lately_invest_time ,invest_amount ,lifecycle ,bank_cards ,platform_id FROM custom_pandect_info p
]]>
<include refid="MS-CUSTOM-PANDECT-INFO-QUERY-WHERE" />
<isNotEmpty property="customPandectInfo.orderBy">
order by $customPandectInfo.orderBy$
</isNotEmpty>
<![CDATA[ LIMIT #limitStart#,#pageSize# ) t
left join user_custom c on (c.custom_id=t.custom_id and c.status='1')
left join user_info d on (d.id=c.user_id and d.status='1')
]]>
</select>
<!--查询满足条件的所有 数据 -->
<select id="MS-CUSTOM-PANDECT-INFO-BY-COUNT" resultClass="long">
<![CDATA[ SELECT count(*) FROM custom_pandect_info p
]]>
<include refid="MS-CUSTOM-PANDECT-INFO-QUERY-WHERE" />
</select>
