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

问题描述：

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

答

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

答

这句话的意思是,如果字段repaymentStatus的值为20则计算urgeNum这一列的最大值,否则计算0这一列的最大值,也就是否则情况下,不计算最大值,注意case when他在里面决定了计算那一列,仔细看就会发现2种情况计算的列不一样,所以此max必须要,若去除则程序必然达不到预期结果!

答

我的理解是，这条sql语句对cusServerId去重了，并且当status值为20查出了usgeNum的最大值，至于目的要结合具体场景看

答

max获取 CASE Status WHEN '20' 之中的最大值

答

去数据库测试一下就好了啊

答

select A.cusServerId,A.name,A.urgeTeamName,A.urgeNum,MAX(A.urgeSuccessNum),MAX(A.urgeSuccessAmount),MAX(A.urgeSuccessRate)/A.urgeNum
from(
select sts.cusServerId,sts.name,sts.urgeTeamName,sum(sts.urgeNum) urgeNum,
CASE Status WHEN '20' THEN urgeNum ELSE 0 END as urgeSuccessNum,
CASE Status WHEN '20' THEN urgeSuccessAmount ELSE 0 END as urgeSuccessAmount,
CASE repaymentStatus WHEN '20' THEN urgeNum ELSE 0 END as 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)

        AND a.release_time  

    group by a.col_admin_id,repayment_status
    ) sts group by sts.cusServerId
    ) A group by A.cusServerId,A.name,A.urgeTeamName,A.urgeNum

干嘛不这么写呢

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

相关推荐