sql求解mysql中的max 跟case when联合

Question

sql如下

MAX(CASE repaymentStatus WHEN '20' THEN urgeNum ELSE 0 END ) urgeSuccessNum,

这个max我感觉一点用没有 各位有用吗。

select sts.cusServerId,sts.name,sts.urgeTeamName,sum(sts.urgeNum) urgeNum,
        MAX(CASE Status WHEN '20' THEN urgeNum ELSE 0 END ) urgeSuccessNum,
        MAX(CASE Status WHEN '20' THEN urgeSuccessAmount ELSE 0 END ) urgeSuccessAmount,
        MAX(CASE repaymentStatus WHEN '20' THEN urgeNum ELSE 0 END )/sum(sts.urgeNum) urgeSuccessRate
        from (
        select  a.col_admin_id cusServerId,f.roleid urgeTeamName,a.col_admin_name name,count(1) urgeNum,
        a.repayment_status repaymentStatus,sum(a.already_repayment_amount)  urgeSuccessAmount
        from t_loan_apply_record a  left join t_uac_user f on a.col_admin_id = f.id
        where  exists (select 1 from t_loan_col_record b where a.id = b.apply_id)

        <if test='dateTo!=null and dateTo!=""'>
            AND a.release_time  <![CDATA[<= #{dateTo} ]]>
        </if>

        group by a.col_admin_id,repayment_status
        ) sts group by sts.cusServerId

Answer 1

是否有更优的解决效果不谈，就本身sql来讲，是有用的。
MAX(CASE Status WHEN '20' THEN urgeNum ELSE 0 END ) urgeSuccessNum,
理解为如果 Status为20，那么效果等同于MAX(urgeNum)结果为urgeNum的最大值，否则计算MAX(0)，结果为0