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

引用

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

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

引用

<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 &gt; 0 then ob.apply_status  end) as sumLoanAppCount,
count(case when ob.apply_status &gt; 1 and ob.apply_status &lt; 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 &gt;= #{registerBeginDate}
          </if>
          <if test="registerEndDate != null">
           AND s.register_date &lt;= #{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 &gt; 0
          </if>
  </where>
 
    </select>

引用

            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