mybatis 对string类型判断比较 group case when then 综合

[quote]

特别注意两点 一个是where 的用法
group的用法 
case when的用法

<if test='hasLoanApplicationFlag == "1"'>
这个对string的比较 是单引号 括起test后面的整串， 然后 == 然后双引号括起比较的字符串内容

[/quote]
[quote]

<select id="querySalesmanAndLoanInfoList" parameterType="com.zyd.orders.placeorder.model.SalesmanDO" 
resultType="com.zyd.orders.placeorder.model.SalesmanDO">


select T.salesmanId, T.salesmanCode, T.salesmanName, T.idNo, T.idType,T.phoneNo, T.channelId,T.registerDate, T.profession, T.email,
T.sumLoanAppCount, T.sumInProgressCount, T.sumFailLoanCount,T.sumReleaseCount,T.sumReleaseAmount ,T.channelName 
from 
(SELECT 
s.salesman_id as salesmanId,
s.salesman_code as salesmanCode,
s.salesman_name as salesmanName,
s.id_no as idNo, 
s.id_type as idType, 
s.phone_no as phoneNo, 
s.channel_id as channelId, 
s.register_date as registerDate, 
s.profession as profession,
s.email as email,
s.login_total_times,
count(case when ob.apply_status > 0 then ob.apply_status end) as sumLoanAppCount, 
count(case when ob.apply_status > 1 and ob.apply_status < 6 then ob.apply_status end) as sumInProgressCount,
count(case when ob.apply_status = 7 then ob.apply_status end) as sumFailLoanCount,
count(case when ob.release_status = 1 then ob.release_status end) as sumReleaseCount,
sum(case when ob.release_status = 1 then ob.release_money end) as sumReleaseAmount,
c.channel_name as channelName
from salesman s left join order_base ob 
on s.salesman_id = ob.user_id 
left join channel_vendor c
on s.channel_id = c.channel_id 
<where>
<if test="salesmanId != null and salesmanId != ''">
AND s.salesman_id = #{salesmanId}
</if>
<if test="phoneNo != null and phoneNo != ''">
AND s.phone_no = #{phoneNo}
</if>
<if test="password != null and password != ''">
AND s.password = #{password}
</if>
<if test="salesmanName != null and salesmanName != ''">
AND s.salesman_name = #{salesmanName}
</if>
<if test="idNo != null and idNo != ''">
AND s.id_no = #{idNo}
</if>
<if test="cardNo != null and cardNo != ''">
AND s.card_no = #{cardNo}
</if>
<if test="loanApplicationCode != null and loanApplicationCode != ''">
AND ob.id = #{loanApplicationCode}
</if>
<if test="registerBeginDate != null">
AND s.register_date >= #{registerBeginDate}
</if>
<if test="registerEndDate != null">
AND s.register_date <= #{registerEndDate}
</if>
</where>
group by s.salesman_id,s.salesman_code,s.salesman_name,s.id_no,s.id_type,s.phone_no,s.channel_id,s.register_date,s.profession,s.email,s.login_total_times 
) T
<where>
<if test='hasLoanApplicationFlag == "1"'>
AND T.sumLoanAppCount > 0
</if>
</where>

</select>

[/quote]

[quote]
select * from (
SELECT 
s.salesman_id as salesmanId,
s.salesman_name as salesmanName,
s.id_no as idNo, 
s.id_type as idType, 
s.phone_no as phoneNo, 
s.channel_id as channelId, 
s.register_date as registerDate, 
s.profession as profession,
s.email as email,
count(case when ob.apply_status = 7 then ob.apply_status else 0 end) as reject_count,
count(case when ob.apply_status > 1 and ob.apply_status < 6 then ob.apply_status else 0 end) as inprogress_count,
count(case when ob.apply_status > 0 then ob.apply_status else 0 end) as total_count,
count(case when ob.release_status = 1 then ob.apply_status else 0 end) as release_status,
sum(case when ob.release_status = 1 then ob.release_money else 0 end) as total_release_amt
from salesman s left join order_base ob
on s.salesman_id = ob.user_id 
where s.salesman_id = 1
group by s.salesman_id,s.salesman_name,s.id_no,s.id_type,s.phone_no,s.channel_id,s.register_date,s.profession,s.email


) t
[/quote]

转载于:https://www.cnblogs.com/zhuyeshen/p/11465648.html